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
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?