mysql - 计算几个派生列的 SUM 总计

Question

我想知道是否有一种方法可以将已计算的派生列的总和添加到新列中。

Employee_KT_State
--------------------------------------------------------
Emp_Id      Team           KT_State            Left_Org
----------------------------------------------------------
 101        Orange         In Progress          2016
 102        Red            Complete             2016
 103        Orange         Complete             N
 104        Green          In Progress          N
 105        Orange         Not Started          N
 106        Green          Not Started          2015
 107        Red            In Progress          N
 108        Red            Complete             N
 109        Green          Complete             N
-----------------------------------------------------------

SELECT
  SUM(if (KT_State = 'In Progress' AND Team = 'Red', 1, 0)) AS 'Red In Progress Count',
  SUM(if (KT_State = 'In Progress' AND Team = 'Green', 1, 0)) AS 'Green In Progress Count',
  SUM(if (KT_State = 'In Progress' AND Team = 'Orange', 1, 0)) AS 'Orange In Progress Count'
   SUM(if (KT_State = 'Complete' AND Team = 'Green', 1, 0)) AS 'Green Complete Count'
    FROM 
       Employee_KT_State

我正在尝试添加绿色“进行中”和“完成计数”。我可以直接使用从第二列和第四列导出的计数，而不是使用另一个具有不同 if 条件的 SUM 吗？

 something like - 

  SELECT
   SUM(if (KT_State = 'In Progress' AND Team = 'Red', 1, 0)) AS 'Red In Progress Count',
   SUM(if (KT_State = 'In Progress' AND Team = 'Green', 1, 0)) AS 'Green In Progress Count',
   SUM(if (KT_State = 'In Progress' AND Team = 'Orange', 1, 0)) AS 'Orange In Progress Count'
   SUM(if (KT_State = 'Complete' AND Team = 'Green', 1, 0)) AS 'Green Complete Count',
   'Green In Progress Count' + 'Green Complete Count' AS ' Green In-Progress and Complete Count'   --> Will this yield me results??
    FROM 
       Employee_KT_State

感谢任何帮助。

Answer 1

在 MySQL 中，您可以将代码简化为:

SELECT SUM(KT_State = 'In Progress' AND Team = 'Red') AS `Red In Progress Count`,
       SUM(KT_State = 'In Progress' AND Team = 'Green') AS `Green In Progress Count`,
       SUM(KT_State = 'In Progress' AND Team = 'Orange') AS `Orange In Progress Count`,
       SUM(KT_State = 'Complete' AND Team = 'Green') AS `Green Complete Count`,
FROM Employee_KT_State;

要获取另外两列，您需要显式使用 SUM() 表达式。

请注意，我更改了列名称以对列别名使用反引号。仅对字符串和日期常量使用单引号。