Sql查询组by（使用where条件获取计数）[重复]

This question already has an answer here:

这个问题在这里已有答案:

• Count based on condition in SQL Server 3 answers

根据SQL Server 3中的条件计算答案

Stuck in group by clause.

陷入group by子句。

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:

我想要Col2的数量使得Col2值为1:预期的输出是:

Col1      Count(Col2)
---------------------
1         1
2         1
3         0
4         0
5         0

Thanks in advanced.

提前致谢。

2 个解决方案

#1

;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

#2

You can use conditional aggregation:

您可以使用条件聚合:

select col1, sum(case when col2 = 1 then 1 else 0 end)
from t
group by col1;