Schema:
| trx_date | merchant | amount | payment_mode |
|---|---|---|---|
| 2022-04-02 | merchant_1 | 150 | CASH |
| 2022-04-02 | merchant_1 | 500 | ONLINE |
| 2022-04-03 | merchant_2 | 450 | ONLINE |
| 2022-04-03 | merchant_1 | 100 | CASH |
| 2022-04-03 | merchant_3 | 600 | CASH |
| 2022-04-05 | merchant_5 | 200 | ONLINE |
| 2022-04-05 | merchant_2 | 100 | ONLINE |
Desired output:
| merchant | cash_amount | online_amount |
|---|---|---|
| merchant_1 | 250 | 500 |
| merchant_2 | 0 | 550 |
| merchant_3 | 600 | 0 |
| merchant_5 | 0 | 200 |
The output I am getting:
| merchant | cash_amount | online_amount |
|---|---|---|
| merchant_1 | 250 | 0 |
| merchant_1 | 0 | 500 |
| merchant_2 | 0 | 550 |
| merchant_3 | 600 | 0 |
| merchant_5 | 0 | 200 |
Here's the query I have tried:
SELECT merchant_id,
(CASE WHEN payment_mode = "cash" THEN sum(amount) else 0 END) AS cash_amount,
(CASE WHEN payment_mode = "online" THEN sum(amount) else 0 END) AS online_amount
FROM details where payment_mode in ('cash', 'online') group by merchant_id, payment_mode;
CodePudding user response：
You are on the right track, but you instead should be summing the CASE expressions, rather than what you are doing now.
SELECT merchant_id,
SUM(CASE WHEN payment_mode = 'cash' THEN amount ELSE 0 END) AS cash_amount,
SUM(CASE WHEN payment_mode = 'online' THEN amount ELSE 0 END) AS online_amount
FROM details
GROUP BY merchant_id
ORDER BY merchant_id;