Return 0 where there is no value with Count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return 0 where there is no value with Count

Author

Topic

ATphonehome
Starting Member

3 Posts

Posted - 2010-12-08 : 16:31:28

I have a query similar to this one:

select a.email, a.user, t.pub_goal, count(title)
from t_user_subscribed t inner join t_user a
on t.user_id = a.user_id left join t_user_assignment x
on x.user_id = a.user_id left join t_assignment p
on x.assignment_id=p.assignment_id
where published > '2010-12-01'
group by a.user, t.pub_goal, a.email

This returns the subscribed users who have produced titles within this month, but it does not return the users who have not published within this month.

How do I make it so that count(title) returns 0 in the rows where the subscribed users haven't produced anything? (I need to be able to show all of the subscribed users and the number of titles, even if the number is 0)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-08 : 16:38:16

Which table is published on? You should always qualify all columns. I'm guessing x.

select a.email, a.user, t.pub_goal, sum(case when title is not nll then 1 else 0 end)
from t_user_subscribed t inner join t_user a
on t.user_id = a.user_id
left join t_user_assignment x
on x.user_id = a.user_id
and x.published > '2010-12-01'
left join t_assignment p
on x.assignment_id=p.assignment_id
group by a.user, t.pub_goal, a.email

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ATphonehome
Starting Member

3 Posts

Posted - 2010-12-08 : 16:43:31

published is on p.

I tried this - it still only returns the two subscribed users that have published - does not return 0 for the # of titles if people have not published.

quote:
Originally posted by nigelrivett

Which table is published on? You should always qualify all columns. I'm guessing x.

select a.email, a.user, t.pub_goal, sum(case when title is not nll then 1 else 0 end)
from t_user_subscribed t inner join t_user a
on t.user_id = a.user_id
left join t_user_assignment x
on x.user_id = a.user_id
and x.published > '2010-12-01'
left join t_assignment p
on x.assignment_id=p.assignment_id
group by a.user, t.pub_goal, a.email

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-08 : 17:01:57

select a.email, a.user, t.pub_goal, sum(case when p.title is not nll then 1 else 0 end)
from t_user_subscribed t
inner join t_user a
on t.user_id = a.user_id
left join t_user_assignment x
on x.user_id = a.user_id
left join t_assignment p
on x.assignment_id=p.assignment_id
and p.published > '2010-12-01'
group by a.user, t.pub_goal, a.email

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ATphonehome
Starting Member

3 Posts

Posted - 2010-12-09 : 13:40:10

Nope, it still doesn't work. I think the date restriction makes it so that users that haven't published articles after 2010-12-01 don't exist in this set of data. I need to make it so that all of the subscribed users show with the count = 0 (instead of no value/row)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources