SQLTeam.com | Weblogs | Forums

SQL query help

I have data in the format below;

Branch Status
A 1
B 1
C 2
B 2
A 1

I need a query to produce the result below;

Branch Total Count Total of Status 2
A 2 0
B 2 1
C 1 1

what is Total of Status 2

declare @mcdani table(Branch char(1), Status int)

insert into @mcdani	
select 'A',	1 union all
select 'B',	1 union all
select 'C',	2 union all
select 'B',	2 union all
select 'A',	1 

select Branch, count(1)
  From @mcdani
  group by Branch

"Total of Status 2" is a column am seeking to get, that will give the count of status 2 for each branch. for example, if you take branch A, it doesn't have status 2, so the count under "Total of Status 2" will be 0, and so on.

select Branch, count(1) [Total Count],
       SUM(CASE WHEN status = 2 THEN 1 else 0 END) [Total of Status 2]
  From @mcdani
  group by Branch

It's working as expected.