count does not return when quantity is zero

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

count does not return when quantity is zero

Author

Topic

port43
Starting Member

19 Posts

Posted - 2011-10-23 : 06:52:58

This query will list ProjectName and ProjectID when there is a quantity of 2 or 1 but not zero. Why not and how can I fix it?

select Projects.ProjectName, Projects.ProjectID, count(ProjectUsers.RoleID) AS 'OnCall Count'
from Projects
join ProjectUsers ON
	Projects.ProjectID = ProjectUsers.ProjectID

where ProjectUsers.RoleID = 'OnCall'
group by Projects.ProjectName, Projects.ProjectID
having count(ProjectUsers.RoleID) < 3
order by Projects.ProjectName

Thank you for your time.

blessings,
Tony <><

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-23 : 09:14:12

[code]
select Projects.ProjectName, Projects.ProjectID, count(ProjectUsers.RoleID) AS 'OnCall Count'
from Projects
LEFT join ProjectUsers ON
Projects.ProjectID = ProjectUsers.ProjectID
~~where~~ AND ProjectUsers.RoleID = 'OnCall'
group by Projects.ProjectName, Projects.ProjectID
having count(ProjectUsers.RoleID) < 3
order by Projects.ProjectName
[/code]

KH
[spoiler]Time is always against us[/spoiler]

port43
Starting Member

19 Posts

Posted - 2011-10-23 : 10:25:53

Thank you khtan. Can you easily describe how those small changes corrected things?

Again, thank you so much for your help.

blessings,
Tony <><

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-23 : 11:07:23

quote:
Originally posted by port43

Thank you khtan. Can you easily describe how those small changes corrected things?

Again, thank you so much for your help.

blessings,
Tony <><

your original query used inner join (or in short join). it means will it will retrieve only those projects which have some details present in projectusers . thats why it returned ones with 2,1 users. In case of project without users it wont have any details in projectusers table. thats why you've to use left join if you want to get records with 0 user count as in that case it will returns all records from project regardless of match in project users.
the reason why condition was moved to AND rather than WHERE is beacuse for unmatched records the field will have NULL values and the condition would filter the off. see explanation below

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

port43
Starting Member

19 Posts

Posted - 2011-10-23 : 11:56:16

Thank you for the explanation. I'll have to read up on "join" in general because I get lost at the "inner" and "outer."

blessings,
Tony <><

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-23 : 12:36:46

Hello port43,

Please just use this select query
COUNT(ISNULL(col1,0)) C2,where col1=ProjectUsers;

I hope that help you passover your problem

paul Tech

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-23 : 12:49:22

quote:
Originally posted by paultech

Hello port43,

Please just use this select query
COUNT(ISNULL(col1,0)) C2,where col1=ProjectUsers;

I hope that help you passover your problem

paul Tech

Please see suggestions given before
the problem is usage of inner join and not NULL values
so unless OP uses left join he wont get required output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-23 : 12:50:09

quote:
Originally posted by port43

Thank you for the explanation. I'll have to read up on "join" in general because I get lost at the "inner" and "outer."

blessings,
Tony <><

ok read below article

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources