Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Do the GROUP BY in 3 derived tables, one for each case, and then join to invoices. Similarly to "Option3 " in this answer: dba.stackexchange.com/questions/17012/help-with-this-query/… –  ypercube Jun 13 '13 at 12:10
    
@ypercube: so there is no way to do it without subquery/tmp table? –  guest Jun 13 '13 at 12:18
    
Yes, there is. @alex07 has already answered. –  ypercube Jun 13 '13 at 12:55

1 Answer 1

up vote 2 down vote accepted

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)

share|improve this answer
    
this is great idea, thank you –  guest Jun 13 '13 at 12:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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