sql - 选择多个 COUNT

Question

在Oracle中，给定以下数据

+------------+-----+
+ STATUS     | GRP +
+------------+-----+
+ Pass       | A   +
+ Fail       | A   +
+ Pass       | A   +
+ Pass       | B   +
+ Fail       | B   +
+ Pass       | C   +
+ bad        | C   +
+------------------+

我想得到如下结果

+---------+-------+-------+-------+
+ GRP     | Total + Pass  + Fail  +
+---------+-------+-------+-------+
+ A       | 3     + 2     + 1     +
+ B       | 2     + 1     + 1     +
+ C       | 2     + 1     + 0     +
+---------+-------+-------+-------+

是否可以通过一个 SQL 查询来完成，还是我需要进行三个单独的 SQL 调用？

Answer 1

您可以使用 SQL 分组以及 COUNT() 和 SUM() 聚合函数来执行此操作。对于 SUM，我们使用嵌入式 CASE 语句的标准 SQL“技巧”。

select GRP, COUNT(*) as Total, 
            SUM(CASE WHEN STATUS = 'Pass' THEN 1 ELSE 0 END) AS Pass,
            SUM(CASE WHEN STATUS = 'Fail' THEN 1 ELSE 0 END) AS Fail
from table
group by GRP

---

平均知道 AVG 聚合将忽略任何为 null 的参数，使用相同的技巧来获取平均值。

select GRP, COUNT(*) as Total, 
            SUM(CASE WHEN STATUS = 'Pass' THEN 1 ELSE 0 END) AS Pass,
            SUM(CASE WHEN STATUS = 'Fail' THEN 1 ELSE 0 END) AS Fail,
            AVG(CASE WHEN STATUS = 'Pass' THEN Score ELSE null END) AS PassAVG,
            AVG(CASE WHEN STATUS = 'Fail' THEN Score ELSE null END) AS FailAVG,
from table
group by GRP