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

TSql Question: Count, Group By and Outer Join

I have a question related with SQL Server. Consider the following TSql statements.

 

drop table #Fun_Groups;

drop table #Fun_UsersGroup;

create table #Fun_Groups (GroupName nvarchar(20));

create table #Fun_UsersGroup (UserID nvarchar(20), GroupName nvarchar(20));

 

insert into #Fun_Groups values ('User');

insert into #Fun_Groups values ('Operator');

insert into #Fun_Groups values ('Executive');

insert into #Fun_Groups values ('Manager');

insert into #Fun_Groups values ('Administrator');

 

insert into #Fun_UsersGroup values ('DemoUser1', 'User');

insert into #Fun_UsersGroup values ('DemoUser2', 'User');

insert into #Fun_UsersGroup values ('DemoUser2', 'Operator');

 

select a.GroupName, count(*) 'Count'

from #Fun_Groups a left outer join #Fun_UsersGroup b on a.groupname=b.groupname

where a.GroupName='Executive'

group by a.GroupName

 

We get the value one for the Count; we are expecting that it should be zero. What’s missing?

Published Thursday, August 26, 2004 5:39 PM by khurram
Filed under:

Comments

# re: TSql Question: Count, Group By and Outer Join

Friday, August 27, 2004 3:47 AM by Sohaib AtharT
Hey Khurram,
How are you doing? :)

You get a 1 because you are doing a left outer join... the 1 actually corresponds to a NULL record (since none of the users from the 2nd table is of the Executive type). If you want the count for all types of users

So try this >>
select *
from
#Fun_Groups a
left join
(select groupname as thegroup, count(*) as total from #Fun_UsersGroup group by GroupName) b
on a.groupname=b.thegroup


which is basically what you are doing (ignoring the where clause)

I think what you want to do is something like >>>

select #fun_groups.groupname, count(#fun_usersgroup.groupname) as [Count]
from #fun_groups left join
#fun_usersgroup
on #fun_groups.groupname = #fun_usersgroup.groupname
group by #fun_groups.groupname

Right?

Sohaib.

# TSql: Count, Group By and Outer Join

Friday, August 27, 2004 9:38 AM by TrackBack
New Comments to this post are disabled