使用具有多个条件的 sql 获取总和 [英] getting sum using sql with multiple conditions

问题描述

我在列中有数据: -

I m having data in columns as: -

  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 = 失败的体积总和.

Now grouping by Process, i want the sum of volume (not taking into account whatever TAT), sum of volume where TAT = Pass, sum of Volume where TAT = Fail.

像这样

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

推荐答案

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

For SQL Server, you can use CASE expressions to conditionally determine the amount you need to add, then SUM them together, like this:

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 或 IIF，如下所示:

For Microsoft Access, CASE isn't supported, so you can use SWITCH or IIF, like so:

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

这篇关于使用具有多个条件的 sql 获取总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！