Assistance with mysql case-CodePudding

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.

CodePudding user response：

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`