How to return NULL values from COUNT function

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

How to return NULL values from COUNT function

Author

Topic

Scottgman
Starting Member

2 Posts

Posted - 2009-09-04 : 11:04:40

I have a table of student test scores from different shools. I want to count the number of students who scored "Advanced" and group the results by school. That part is easy. The problem is that some schools do not have any students who scored "Advanced" and therefore do not show-up in the result set. However, I want all schools to be listed even if their count is zero. How do I do this? I know it's simple if/then logic but I can't figure out how to implement the logic in SQL Server.

The code below shows the basic query that I'm using. How do I get the schools with zero "Advanced" students to display in my results?


SELECT SchoolName, COUNT(TestScore) AS ScoreCount
FROM StudentTable
WHERE TestScore = 'Advanced'
GROUP BY SchoolName

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-09-04 : 11:09:46

SELECT SchoolName, sum(case when TestScore = 'Advanced' then 1 else 0 end) AS ScoreCount
FROM StudentTable
GROUP BY SchoolName

Scottgman
Starting Member

2 Posts

Posted - 2009-09-04 : 11:21:13

THANK YOU!!!!!

I knew it would be something simple... doh!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources