How to create the multiple sum query with joins

Question

Please consider following database:

Table `invoices`:
-- invoice_id

Table `amounts`:
-- amount_id invoice_id type_id amount

I am trying to get the sum for each type from table like this:

SELECT
    invoice_id,
    SUM(amounts1.amount),
    SUM(amounts2.amount),
    SUM(amounts3.amount)
FROM
    invoices
LEFT JOIN
    amounts amounts1 ON (amounts1.invoice_id = invoices.invoice_id AND amounts1.type_id IN (1,2,3))
LEFT JOIN
    amounts amounts2 ON (amounts2.invoice_id = invoices.invoice_id AND amounts2.type_id IN (4,5,6))
LEFT JOIN
    amounts amounts3 ON (amounts3.invoice_id = invoices.invoice_id AND amounts3.type_id IN (7,8,9))
GROUP BY
    invoices.invoice_id

This query returns unexpected results. Result is different when I remove one of the joins:

SELECT
    invoice_id,
    SUM(amounts1.amount) // returns different amount
FROM
    invoices
LEFT JOIN
    amounts amounts1 ON (amounts1.invoice_id = invoices.invoice_id AND amounts1.type_id IN (1,2,3))
GROUP BY
    invoices.invoice_id

Any ideas what I am doing wrong?

Same thing with COUNT(amounts1.amount_id)

Edit: I realized problem occurs when there is one type_id=1 amount and more than one type_id=4 amount - amount type_id=1 is added twice. Like this:

amount_id amount type_id invoice_id
    51     2.99     1        2          // SUM(amounts.amount1) returns 8.97
    52     2.99     4        2
    53     2.99     4        2
    54     2.99     4        2

How to avoid it?

Answer 1

I believe you can avoid joining the same table 3 times by

SELECT
    i.invoice_id,
    SUM(CASE 
        WHEN a.type_id IN (1,2,3) THEN a.amount
        ELSE 0 END) AS sum1,
    SUM(CASE 
        WHEN a.type_id IN (4,5,6) THEN a.amount
        ELSE 0 END) AS sum2,
    SUM(CASE 
        WHEN a.type_id IN (7,8,9) THEN a.amount
        ELSE 0 END) AS sum3
FROM
    invoices AS i
LEFT JOIN
    amounts AS a ON a.invoice_id = i.invoice_id 
                AND a.type_id IN (1,2,3,4,5,6,7,8,9)
GROUP BY
    i.invoice_id ;

Note: depends on your requirements, LEFT JOIN can be replaced with INNER (then you won't have invoices without amounts in resultset)