TSql: Count, Group By and Outer Join
Earlier I asked a SQL query related question by presenting a scenario. We were having unexpected results with the query that had an outer join, group by and the count.
The actual problem was that we need the counts of all the Groups, if there is no user in particular Group, we should have zero against it, something like
User | 2
Operator | 1
Executive | 0
For this reason, I used left outer join, keeping Groups table on the left. I simplified the test case by using where clause, that I guess is confusing.
The thing I wanted to highlight; that in routine, people use count(*) that may not give correct results in all cases, especially when using outer joins and there are no matching entries in the dependent table. We should always try counting the particular field of interest.
Considering the scenario, we may use count(b.UserID), that will give correct results. If we use count(*), it will give one, as there exists one row in the result of the query against each tupple of the table against which we are having an outer join.