sql - 如何在一行中获得总计和小计？

Question

这是预期的输出。

ID | GRAND_TOTAL | CHANNEL_1 | CHANNEL_2
1  | 100.00      | 50.00     | 50.00
2  | 500.00      | 100.00    | 400.00

但是我的查询结果是这样的:

ID | GRAND_TOTAL | CHANNEL_1 | CHANNEL_2
1  | 100.00      | 50.00     | 0
2  | 100.00      | 0         | 50.00
1  | 500.00      | 100.00    | 0
2  | 500.00      | 0         | 400.00

这是我在查询中尝试的。

SELECT 
       ID, 
       SUM(Amount) AS GRAND_TOTAL,
       CASE WHEN CHANNEL_ID = 1 THEN AMOUNT ELSE 0 END CHANNEL_1,
       CASE WHEN CHANNEL_ID = 2 THEN AMOUNT ELSE 0 END CHANNEL_2
FROM
       CHANNEL_AMOUNT
GROUP BY
       ID, CHANNEL_ID, AMOUNT

Answer 1

GROUP BY ____ 表示“我希望每个 ____ 一个结果行”。您不希望每个 ID、 channel 和数量一个结果行，而是每个 ID 一个。因此:GROUP BY id。然后你想要每个 channel 的总和，所以使用SUM。

SELECT
  id,
  SUM(amount) AS grand_total,
  SUM(CASE WHEN channel_id = 1 THEN amount ELSE 0 END) AS channel_1,
  SUM(CASE WHEN channel_id = 2 THEN amount ELSE 0 END) AS channel_2
FROM channel_amount
GROUP BY id
ORDER BY id;

(在标准 SQL 中，这将是 SUM(amount) FILTER (WHERE channel_id = 1)，但是 SQL Server 还没有 filter 子句，所以我们在聚合中使用 case 表达式功能。)