I have the following table1 invoice
1 | 2022-12-05 | 20
2 | 2022-12-06 | 100
3 | 2022-12-07 | 100
And table2 invoice_payment
1 | 1 | 20 | cash
2 | 2 | 100 | POS
3 | 3 | 25 | Cash
4 | 3 | 50 | POS
5 | 3 | 25 | Cash
I am trying to get each invoice with the amount paid and the breakdown of the payments
I have tried
`SELECT invoice_id, count(invoice_id) as transactions,
(CASE WHEN payment_type = 'Cash' THEN SUM(paid) END) AS paid_cash,
(CASE WHEN payment_type = 'Insurance' THEN SUM(paid) END) AS paid_ins,
(CASE WHEN payment_type = 'POS' THEN SUM(paid) END) AS paid_pos,
(CASE WHEN payment_type = 'Chq' THEN SUM(paid) END) AS paid_chq,
(CASE WHEN payment_type = 'BT' THEN SUM(paid) END) AS paid_bt
FROM
invoice_payment ipc
INNER JOIN
invoice i
ON ipc.invoice_id = i.id
GROUP BY invoice_id DESC`
This is the output from the above query
1 | 1 | 20 |NULL | NULL |NULL |NULL
2 | 1 | NULL |NULL | 100 |NULL |NULL
3 | 3 | 100 |NULL | NULL |NULL |NULL
However, the desired output would look like
1 | 1 | 20 |NULL | NULL |NULL |NULL
2 | 1 | NULL |NULL | 100 |NULL |NULL
3 | 3 | 50 |NULL | 50 |NULL |NULL
How do i adjust this code the get the desired output? Currently the written code is not getting the sum of the different payment types.
SELECT invoice_id, count(invoice_id) as transactions,
sum(CASE WHEN payment_type = 'Cash' THEN paid END) AS paid_cash,
sum(CASE WHEN payment_type = 'Insurance' THEN paid END) AS paid_ins,
sum(CASE WHEN payment_type = 'POS' THEN paid END) AS paid_pos,
sum(CASE WHEN payment_type = 'Chq' THEN paid END) AS paid_chq,
sum(CASE WHEN payment_type = 'BT' THEN paid END) AS paid_bt
FROM
invoice_payment ipc
INNER JOIN
invoice i
ON ipc.invoice_id = i.id
GROUP BY invoice_id DESC`