Welcome to weblogs.com.pk Sign in | Join | Help

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.

Published Friday, August 27, 2004 9:36 AM by khurram
Filed under:

Comments

No Comments

New Comments to this post are disabled