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 |
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.
Thanks