Help with SQL Query - 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 2005 Forums

Transact-SQL (2005)

Help with SQL Query

Author

Topic

almeidag
Starting Member

2 Posts

Posted - 2010-07-14 : 10:45:22

Hi all,

I have this simple query:

SELECT StateId, count(*)
FROM REQUISITION
GROUP BY StateId
ORDER BY StateId

This returns:

3 1
9 24

So I have 1 row with the StateId = 3 and 24 rows with StateId = 9. So far so good.

Now comes the tricky part:

I need to build a query to return this:

1 0
2 0
3 1
4 0
5 0
6 0
7 0
8 0
9 24

I need to ALWAYS return 9 rows even if not all StateId are present in the main table. I was wondering if this is possible using a query or if I have to use programming to maniulate the result set.

thanks in advance

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-14 : 11:09:44

DECLARE @Table Table (Stateid smallint)

INSERT INTO @TABLE

Select 1 UNION ALL
Select 1 UNION ALL
Select 1 UNION ALL
Select 1 UNION ALL
Select 1 UNION ALL
Select 1 UNION ALL
Select 1 UNION ALL
Select 1 UNION ALL
Select 1 UNION ALL
Select 1 UNION ALL
Select 1 UNION ALL
Select 2 UNION ALL
Select 2 UNION ALL
Select 2 UNION ALL
Select 2

Select spt.number,isnull(count(stateid),0) as Recs
from
master..spt_values spt
left join
@table t
on
spt.number = t.stateid

where spt.[type] = 'P' and spt.number between 0 and 9

group by spt.number

Jim

Everyday I learn something that somebody else already knew

almeidag
Starting Member

2 Posts

Posted - 2010-07-14 : 11:28:29

Thanks jimf, that's exactly what I was looking for.

regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources