CODEWITHSUNDEEP
sqlsql-server
shergill
shergill

Reputation: 3788

Gathering multiple statistics about a table in a single query

Let's say I have this data in a 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 do another query
select type,count(*) from table where status = 'closed'

a          2
c          1

How do I write a query that shows me a table like this

type        open         closed
a            1             2
b            2             0
c            0             1

Upvotes: 1

Views: 185

Answers (3)

Eric H
Eric H

Reputation: 1789

Created your table in SQL 2005 and confirmed the below works using the pivot command:

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

Upvotes: 0

Tony
Tony

Reputation: 10347

You can use PIVOT, have a look at the BOL or search any SQL site for some example of use.

Upvotes: 0

bobs
bobs

Reputation: 22214

This will produce your 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

Upvotes: 3

Related Questions