SQL分组依据-从单个列生成多个聚合列 [英] SQL Group By - Generate multiple aggregate columns from single column

问题描述

我想按公司&设置日期并为两个单独的值（Flag = Y和Flag = N）生成计数列。
输入表如下所示：

I would like to group by Company & Date and generate count columns for 2 separate values (Flag=Y and Flag=N). Input table looks like this:

Company  Date   Flag
------- ------- -----
001      201201  Y
001      201201  N
001      201202  N
001      201202  N
001      201202  Y

输出应如下所示：

Company Date   Count_Y Count_N
------- ------ ------- -------
001     201201  1       1
001     201202  1       2

如何编写SQL查询？
任何帮助都将受到赞赏！谢谢！

How can I write the SQL query? Any kind of help is appreciated! Thanks!

推荐答案

您可以使用以下相关子查询来做到这一点：

You can do it using correlated subqueries like this:

SELECT 
  Company, 
  Date, 
  (SELECT COUNT(*) FROM MyTable AS T1 
     WHERE T1.Flag='Y' AND T1.Company=T2.Company AND T1.Date=T2.Date) AS Count_Y,
  (SELECT COUNT(*) FROM MyTable AS T1 
     WHERE T1.Flag='N' AND T1.Company=T2.Company AND T1.Date=T2.Date) AS Count_N
FROM MyTable AS T2
GROUP BY Company, Date

您也可以更简洁地执行此操作，但是使用 SUM 技巧可能（可以说）会大大降低可读性：

You can also do it more concisely, but perhaps with (arguably) slighly less readability using the SUM trick:

SELECT 
  Company, 
  Date, 
  SUM(CASE WHEN Flag='Y' THEN 1 ELSE 0 END) AS Count_Y,
  SUM(CASE WHEN Flag='N' THEN 1 ELSE 0 END) AS Count_N,
FROM MyTable
GROUP BY Company, Date

在Oracle / PLSQL中， DECODE 函数可用于替换 Case 更加简洁：

In Oracle/PLSQL, the DECODE function can be used to replace the CASE for the even more concise:

SELECT 
  Company, 
  Date, 
  SUM(DECODE(Flag,'Y',1,0)) AS Count_Y,
  SUM(DECODE(Flag,'N',1,0)) AS Count_N,
FROM MyTable
GROUP BY Company, Date

这篇关于SQL分组依据-从单个列生成多个聚合列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！