Using ELSE in CASE Statements - SQL Server Forums

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

Using ELSE in CASE Statements

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2015-03-09 : 12:38:08

This works EXCEPT when I add the ELSE line.

How do I accomplish the ELSE?

Does the ELSE have to state "WHEN [BG_STATUS] <> 'Blocked' and [BG_STATUS] <> 'Closed', etc? Do I have to delineate for the ELSE statement everything that BG_STATUS is *not* equal to? Seems there ought to be a way but I can't find it.



SELECT BG_SEVERITY AS 'Severity',

       SUM(CASE WHEN [BG_STATUS] = 'Blocked' THEN 1 ELSE 0 END) as 'Blocked',

       SUM(CASE WHEN [BG_STATUS] = 'Closed' THEN 1 ELSE 0 END) as 'Closed',

       SUM(CASE WHEN [BG_STATUS] = 'Customer Test' THEN 1 ELSE 0 END) as 'Customer Test',

       SUM(CASE WHEN [BG_STATUS] = 'Deferred' THEN 1 ELSE 0 END) as 'Deferred',

       SUM(CASE WHEN [BG_STATUS] = 'In Progress' THEN 1 ELSE 0 END) as 'In Progress',

       SUM(CASE WHEN [BG_STATUS] = 'New' THEN 1 ELSE 0 END) as 'New',

       SUM(CASE WHEN [BG_STATUS] = 'Open' THEN 1 ELSE 0 END) as 'Open',

       SUM(CASE WHEN [BG_STATUS] = 'Pending' THEN 1 ELSE 0 END) as 'Pending',

       SUM(CASE WHEN [BG_STATUS] = 'Ready for Test' THEN 1 ELSE 0 END) as 'Ready for Test',

       SUM(CASE WHEN [BG_STATUS] = 'Rejected' THEN 1 ELSE 0 END) as 'Rejected',

       SUM(CASE WHEN [BG_STATUS] = 'Reopen' THEN 1 ELSE 0 END) as 'Reopen',

       SUM(CASE ELSE 1]

FROM

       BUG

WHERE BG_DETECTION_DATE BETWEEN '1/1/2015' and '2/28/2015'

GROUP BY BG_SEVERITY

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-09 : 13:08:43

What you have is correct.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2015-03-09 : 13:21:08

quote:
Originally posted by tkizer

What you have is correct.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

But it returns an error:

Incorrect syntax near the keyword 'ELSE'

... so it can't be correct.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-09 : 13:27:51

I misunderstood your question. I thought you were asking about the repeated use of ELSE 0. I see you are asking about the final SUM/CASE. You'll need to list them all out in a CASE.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2015-03-09 : 13:37:25

OK, got it ... that's what I wanted to avoid but there seems to be no way to avoid it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources