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.
Author |
Topic |
Steve2106
Posting Yak Master
183 Posts |
Posted - 2012-11-16 : 17:40:45
|
Hi There,I need to count more than 1 thing. This code gives me the first part of what I need:SELECT dbo.tbLocSource.SourceDescription, COUNT(*) AS Expr1FROM dbo.tbLocal INNER JOINdbo.tbLocSource ON dbo.tbLocal.SourceId = dbo.tbLocSource.SourceIdGROUP BY dbo.tbLocSource.SourceDescriptionORDER BY Expr1 DESCBut now I need to count the number of High, Medium and Low there are on the count returned.Thanks for your help.Best Regards,Steve |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-16 : 17:55:15
|
If High, Medium and Low are values in SourceDescription you can do thisSELECT SUM(CASE WHEN SourceDesription = 'High' THEN 1 ELSE 0 END) AS HIGH, SUM(CASE WHEN SourceDesription = 'Medium' THEN 1 ELSE 0 END) AS Medium, SUM(CASE WHEN SourceDesription = 'Low' THEN 1 ELSE 0 END) AS Low,COUNT(*)FROM dbo.tbLocal INNER JOINdbo.tbLocSource ON dbo.tbLocal.SourceId = dbo.tbLocSource.SourceIdGROUP BY SourceDescriptionJimEveryday I learn something that somebody else already knew |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-16 : 22:38:39
|
quote: Originally posted by jimf If High, Medium and Low are values in SourceDescription you can do thisSELECT SUM(CASE WHEN SourceDesription = 'High' THEN 1 ELSE 0 END) AS HIGH, SUM(CASE WHEN SourceDesription = 'Medium' THEN 1 ELSE 0 END) AS Medium, SUM(CASE WHEN SourceDesription = 'Low' THEN 1 ELSE 0 END) AS Low,COUNT(*)FROM dbo.tbLocal INNER JOINdbo.tbLocSource ON dbo.tbLocal.SourceId = dbo.tbLocSource.SourceIdGROUP BY SourceDescriptionJimEveryday I learn something that somebody else already knew
this will simply give a 1 against high,medium,low depending on what description value is due to group by on description fieldmy guess is high,low,etc are in some other fieldotherwise op will just get single row with all counts by removing the group by------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Steve2106
Posting Yak Master
183 Posts |
Posted - 2012-11-17 : 05:36:41
|
Hi Visakh,As always, you are correct.The high, medium and low is stored in a field called priority on each record.Any idea how I can do this.Best Regards,Steve |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-11-17 : 06:05:07
|
Then replace that column with Jim's codeSELECTSUM(CASE WHEN priority = 'High' THEN 1 ELSE 0 END) AS HIGH, SUM(CASE WHEN priority = 'Medium' THEN 1 ELSE 0 END) AS Medium, SUM(CASE WHEN priority = 'Low' THEN 1 ELSE 0 END) AS Low,COUNT(*)FROM dbo.tbLocal INNER JOINdbo.tbLocSource ON dbo.tbLocal.SourceId = dbo.tbLocSource.SourceIdGROUP BY SourceDescriptionMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|