Boardman411 Boardman411 - 19 days ago 6
SQL Question

SQL - Count Results of 2 Columns

I have the following table which contains ID's and UserId's.

ID UserID
1111 11
1111 300
1111 51
1122 11
1122 22
1122 3333
1122 45


I'm trying to count the distinct number of 'IDs' so that I get a total, but I also need to get a total of ID's that have also seen the that particular ID as well... To get the ID's, I've had to perform a subquery within another table to get ID's, I then pass this into the main query... Now I just want the results to be displayed as follows.

So I get a Total No for ID and a Total Number for Users ID - Also would like to add another column to get average as well for each ID

TotalID Total_UserID Average
2 7 3.5


If Possible I would also like to get an average as well, but not sure how to calculate that. So I would need to count all the 'UserID's for an ID add them altogether and then find the AVG. (Any Advice on that caluclation would be appreciated.)

Current Query.

SELECT DISTINCT(a.ID)
,COUNT(b.UserID)
FROM a
INNER JOIN b ON someID = someID
WHERE a.ID IN ( SELECT ID FROM c WHERE GROUPID = 9999)
GROUP BY a.ID


Which then Lists all the IDs and COUNT's all the USERID.. I would like a total of both columns. I've tried warpping the query in a

SELECT COUNT(*) FROM (


but this only counts the ID's which is great, but how do I count the USERID column as well

Answer

You seem to want this:

SELECT COUNT(DISTINCT a.ID), COUNT(b.UserID),
       COUNT(b.UserID) * 1.0 / COUNT(DISTINCT a.ID)
FROM a INNER JOIN
     b
     ON someID = someID
WHERE a.ID IN ( SELECT ID FROM c WHERE GROUPID = 9999);

Note: DISTINCT is not a function. It applies to the whole row, so it is misleading to put an expression in parentheses after it.

Also, the GROUP BY is unnecessary.

The 1.0 is because SQL Server does integer arithmetic and this is a simple way to convert a number to a decimal form.

Comments