带条件的 SQL SUM 组

Question

我的 SQL Server 表名称 tbInvoice 如下所示:

我期望的结果是这样的:

其想法是根据供应商分组的所有行的total_inv+total_1+total_2计算GRAND TOTAL，其中状态为0(status_inv、status_1、status_2)。

我做了类似的事情，但没有成功:

SELECT DISTINCT vendor, 
 SUM(CASE WHEN total_inv = 0 AND total_1 = 0, and total_2 = 0 THEN (total_inv + total_1 + total_2)  
          WHEN total_inv = 0 AND total_1 = 0, and total_2 = 1 THEN (total_inv + total_1)  
          WHEN total_inv = 0 AND total_1 = 1, and total_2 = 0 THEN (total_inv + total_2)
          WHEN total_inv = 0 AND total_1 = 1, and total_2 = 1 THEN (total_inv)
          WHEN total_inv = 1 AND total_1 = 0, and total_2 = 0 THEN (total_1 + total_2)  
          WHEN total_inv = 1 AND total_1 = 0, and total_2 = 1 THEN (total_1)  
          WHEN total_inv = 1 AND total_1 = 1, and total_2 = 0 THEN (total_2)
          WHEN total_inv = 1 AND total_1 = 1, and total_2 = 1 THEN 0 END) GRAND TOTAL FROM tbInvoice GROUP BY vendor  

非常感谢。

Answer 1

您混淆了 CASE 表达式中的状态和总计。而且，你让事情变得比必要的更加复杂。您想要在状态为 0 时累加总计:

SELECT
  vendor, 
  SUM(CASE WHEN status_inv = 0 THEN total_inv ELSE 0 END) +
      CASE WHEN status_1   = 0 THEN total_1   ELSE 0 END) +
      CASE WHEN status_2   = 0 THEN total_2   ELSE 0 END)) AS grand_total
FROM tbInvoice
GROUP BY vendor  
ORDER BY vendor;

没有任何数学技巧;-)