使用具有多个条件的sql获取总和

我的列中的数据为：-

  Process   Volume      TAT
  1            1        Pass
  1            2        Fail
  2            5        Fail
  2            5        Pass
  3            1        Pass
  4            6        Fail
  4            4        Pass

现在按流程分组，我想要体积总和（不考虑任何 TAT）、TAT = 通过的体积总和、TAT = 失败的体积总和。

像这样

Process     Total Volume    Volume(TAT=Pass)    Volume(TAT = Fail)
1           3               1                   2
2           10              5                   5
...
...

对于 SQL Server，您可以使用CASE表达式有条件地确定您需要添加的数量，然后SUM他们在一起，就像这样：

SELECT Process, SUM(Volume) AS TotalVolume, 
    SUM(CASE WHEN TAT = 'Pass' THEN Volume ELSE 0 END) AS Pass,
    SUM(CASE WHEN TAT = 'Fail' THEN Volume ELSE 0 END) AS Fail
FROM (
     -- Dummy data here for testing
    SELECT 1 AS Process, 1 as Volume, 'Pass' AS TAT
    UNION SELECT 1, 2, 'Fail'
    UNION SELECT 2, 5, 'Fail'
    UNION SELECT 2, 5, 'Pass'
    UNION SELECT 3, 1, 'Pass'
    UNION SELECT 4, 6, 'Fail'
    UNION SELECT 4, 4, 'Pass'
) MyTable
GROUP BY Process
ORDER BY Process

对于 Microsoft Access，CASE不支持，所以你可以使用SWITCH or IIF，像这样：

SUM(IIF(TAT = 'Pass', Volume, 0)) AS Pass