Counts for same column... - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Counts for same column...

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2005-02-12 : 18:37:04

Hello...

I have a table with a column that contains an integer value (of 0 to 4) that indicates the severity level. I am trying to figure out how I can return a set of results for the count of each severity level and even show if there are no rows for a severity.

When I use something like:
SELECT COUNT(severityLevel) FROM Table GROUP BY severityLevel

I only get the severityLevels that have any associated rows. Plus, even when I add an ORDER BY clause, the order is not in the real order of 0 to 4.

The only other way I could figure how to do this....was to create a Temp Table in my stored proc and run 5 different SELECTS (one for each severityLevel) and then return the Temp Table results.

Does anyone know of a better or easier way?

thanks
- dw

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-12 : 18:44:30

how bout
select sum(case when severityLevel = 1 then 1 else 0 end)
, sum(case when severityLevel = 2 then 1 else 0 end)
, sum(case when severityLevel = 3 then 1 else 0 end)
, sum(case when severityLevel = 4 then 1 else 0 end)
, sum(case when severityLevel is null or severityLevel = 0 then 1 else 0 end)
from MyTable

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-12 : 19:11:27

I must be missing something - wouldn't this work?

Select severityLevel, count(*) from MyTable group by SeverityLevel order by severityLevel

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-12 : 19:26:12

probably the same thing i just pivoted it

Go with the flow & have fun! Else fight the flow

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2005-02-12 : 20:17:16

Yes...this works...and, in fact, better than the one I posted originally. SO thanks! However, if none of the rows in the table have a severityLevel of say "3", then I still do not get any results to indicate that there are no rows with a level of 3. But...maybe I can handle this in my application. Thanks again!
....dw

quote:
Originally posted by TG

I must be missing something - wouldn't this work?

Select severityLevel, count(*) from MyTable group by SeverityLevel order by severityLevel

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-12 : 20:31:49

well mine gives you that info even if it is pivoted, no?

Go with the flow & have fun! Else fight the flow

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2005-02-13 : 17:49:27

quote:
Originally posted by spirit1

well mine gives you that info even if it is pivoted, no?

Go with the flow & have fun! Else fight the flow

Yes, yours works perfectly. I'll probably use it since it does all of the processing the DB server and I won't have to have any specific code to handle whether all of the severity levels are extant.

thanks again!

Kristen
Test

22859 Posts

Posted - 2005-02-14 : 01:27:27

If the Severity Levels existed in a lookup table you could use that - then you wouldn;t have to change the code when a new severity level is added!


SELECT MLT.severityLevel, 
       COUNT(*) 
FROM   MyLookupTable MLT
       LEFT OUTER JOIN MyTable MT
           ON MT.severityLevel = MLT.severityLevel
GROUP BY MLT.SeverityLevel 
ORDER BY MLT.severityLevel

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources