Geek Answers Handbook

Collecting multiple table statistics in a single query

Say I have this data in an MSSQL table

  type status       
 a open
 b open
 a closed
 a closed
 a closed
 b open
 c closed

I can run this query to get a table like this. select type,count(*) from table where status = 'open'

  a 1
 b 2

Then I can execute another request
select type,count(*) from table where status = 'closed'

  a 2
 c 1

How to write a query that shows me a table like this

  type open closed
 a 1 2
 b 2 0
 c 0 1
+1
source share
3 answers

This will give the desired result.

 select type, SUM(case when status = 'open' then 1 else 0 end) as [Open], SUM(case when status = 'closed' then 1 else 0 end) as [Closed] from table group by type
+3
source

You can use PIVOT , look at BOL, or find any SQL site for some use case.

0
source

Created your table in SQL 2005 and confirmed the following work with the pivot command:

 select * from (select type, status from table) test pivot ( count(status) for status in ([Open], [Closed] )) pivottable
0
source

More articles:


All Articles