How to get the grand total and the subtotal in one row?-CodePudding

Here is the expected output.

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

But my query resulted like this:

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

This is what I tried in my query.

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

CodePudding user response：

GROUP BY ____ means "I want one result row per ____". You don't want one result row per ID, cannel and amount, but only one per ID. Hence: GROUP BY id. Then you want sums per channel, so use 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;

(In standard SQL this would be SUM(amount) FILTER (WHERE channel_id = 1), but SQL Server doesn't feature the filter clause yet, so we use a case expression inside the aggregation function.)

CodePudding user response：

Looks like you want grouping by Id only

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

CodePudding user response：

You can just try GROUP BY ID.

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