1

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?

4

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.