Get the count of a fixed set of possibilities

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

Get the count of a fixed set of possibilities

Author

Topic

krausr79
Starting Member

19 Posts

Posted - 2012-11-01 : 15:38:35

I'm going to start by getting a sum of statuses, so I can see how many of each status there is:

select sb2,count(sb2) as howmany from qg.temp group by sb2

That might give a result like:

d 5
e 11
g 3

I'd like for a certain set of statuses to be reported on, even if they aren't in the database yet:

a [null] or 0
d 5
e 11
g 3

I'm not allowed to make tables or temporary tables :( . I can't tease this list out of the current database because not all possible statuses have been used yet. I was kind of hoping to do something like this:

select stat, howmany from ('a','d','e','g') as stat left join (select sb2,count(sb2) as howmany from qg.temp group by sb2) as x on stat=sb2

I know that's not right, but is there some way to rig up a column of data to join with? Maybe some other method I havn't considered yet?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-01 : 15:49:02

This:

SELECT a.sb2,COUNT(b.sb2)
FROM 
(
	SELECT 'a' UNION ALL SELECT 'b' 
	UNION ALL SELECT 'c' UNION ALL SELECT 'd'
)a(sb2)
LEFT JOIN YourTable b ON a.sb2 = b.sb2
GROUP BY a.sb2;

If you are on SQL 2008 or later, you can shorten the syntax to

SELECT a.sb2,COUNT(b.sb2)
FROM 
(
	VALUES ('a'),('b'),('c'),('d')
)a(sb2)
LEFT JOIN YourTable b ON a.sb2 = b.sb2
GROUP BY a.sb2;

krausr79
Starting Member

19 Posts

Posted - 2012-11-01 : 15:57:49

Works! Thanks for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-03 : 21:52:31

do you mean you dont have master table for statuses?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources