SQL SELECT查询与组和不同

I have table with 4 columns (id, bm, name, act).

我有4列的表（id，bm，name，act）。

I want to retrieve records grouped by bm and count how many records have of every group where act = no and where act = yes at once...

我想检索按bm分组的记录，并计算每个组中有多少条记录，其中act = no和where act = yes一次...

So if I have records:

所以，如果我有记录：

(Id, bm, name, act)
1, 5, Nik, yes
2, 6, Mike, yes
3, 5, Tom, no
4, 5, Alex, no

Result I need is:

我需要的结果是：

(bm, totalYes, totalNo)
5, 1, 2
6, 1, 0

I guess that everything is possible to retrieve from SQL but I don't know how :(

我想一切都可以从SQL检索，但我不知道如何:(

3 个解决方案

#1

You can use conditional aggregation to achieve this result:

您可以使用条件聚合来实现此结果：

select 
  bm, 
  sum(case when act = 'yes' then 1 else 0 end) as "Count of yes",
  sum(case when act = 'no'  then 1 else 0 end) as "Count of no"
from t
group by bm;

With some databases, like MySQL, you can reduce the aggregation to sum(act = 'yes'), but the ANSI SQL standard requires the full case expression.

对于某些数据库，如MySQL，您可以将聚合减少为sum（act ='yes'），但ANSI SQL标准需要完整的case表达式。

Sample SQL Fiddle

示例SQL小提琴

#2

Try following

试试以下

SELECT SUM(CASE WHEN act = 'no' THEN 1 ELSE 0 END) as NoCount, 
SUM(CASE WHEN act = 'yes' THEN 1 ELSE 0 END) YesCount 
FROM tbl 
GROUP BY gm

#3

Since aggregate functions typically skip nulls, you can create two columns TotalYes and TotalNo, with a value NULL for cases you don't want to include in your count.

由于聚合函数通常会跳过空值，因此您可以创建两列TotalYes和TotalNo，对于您不希望包含在计数中的情况，值为NULL。

Here's what I mean:

这就是我的意思：

SELECT 
     bm, 
     COUNT( CASE act WHEN 'yes' THEN 1 ELSE NULL END ) TotalYes,
     COUNT( CASE act WHEN 'no' THEN 1 ELSE NULL END ) TotalNo
FROM tbl
GROUP BY bm

#1