Stuck in group by clause.
Col1 Col2
----------------
1 1
1 2
2 1
3 3
4 2
5 4
2 3
I want count of Col2 such that Col2 value is 1: The output expected is:
Col1 Count(Col2)
---------------------
1 1
2 1
3 0
4 0
5 0
;with
d as (
select * from (values
(1, 1),
(1, 2),
(2, 1),
(3, 3),
(4, 2),
(5, 4),
(2, 3)) x (Col1, Col2)
)
select distinct d1.Col1, isnull(cnt, 0) cnt
from d d1
left join (
select Col1, COUNT(Col2) cnt
from d d1
where Col2=1
group by Col1, col2
) d2 on d1.Col1 = d2.Col1
order by 1,2
Output:
Col1 Cnt
1 1
2 1
3 0
4 0
5 0
You can use conditional aggregation:
select col1, sum(case when col2 = 1 then 1 else 0 end)
from t
group by col1;
