How can i get a 'count' where the count = 0?

[bmacbmac]

Hi, I have two tables #users and #activity:

create table #users (name varchar(25), printcount int)
go
insert into #users (name) values ('bob')
insert into #users (name) values ('jim')
insert into #users (name) values ('alex')


create table #activity (name varchar(25), description varchar(25))
go
insert into #activity values ('bob', 'printed')
insert into #activity values ('bob', 'printed')
insert into #activity values ('bob', 'printed')
insert into #activity values ('bob', 'searched')
insert into #activity values ('alex', 'printed')
insert into #activity values ('alex', 'printed')
insert into #activity values ('alex', 'searched')
insert into #activity values ('alex', 'searched')
insert into #activity values ('alex', 'printed')

I would like to update #users.printcount to reflect the number of times each user printed in #activity.description.

I tried the following to get my results:

select #users.name, count (#activity.description)
from #users
left outer join #activity
on #activity.name = #users.name
where description = 'printed'
group by #users.name

But the results only shows;
Alex, 3
bob, 3

I would like the results to show a 0 count for jim:
alex, 3
bob, 3
jim, 0

Any idea how I can change my script to reflect this?

SQL 2000

Thanks,

Brian

 

[gmmastros]

Try this...

select #users.name, count (#activity.description)
from #users
left outer join #activity
on #activity.name = #users.name
[!]and[/!] description = 'printed'
group by #users.name

Note that I changed the WHERE to and.

This effectively applies the description = 'printed' filter to the join condition instead of the where condition.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Perfect! Thanks!

 

[Olaf Doschke]

The reason why filtering with WHERE eleminates the record for jim is, that you apply the condition to the left joined intermediate result, which has no data from #activity for jim, and thus is not fulfilling any condition on #activity data. You turn an outer join into an inner join with a where condition on a joined table, as that always eleminates all records with no joined data.

Putting the condition into the join clause makes it a further join condition, which in case of jim still means no joined data, but leaves him in the result with a 0 count.

Bye, Olaf.