Reputation: 737
Similar question to another question except that the three tables I have are hierarchical rather than two tables side by side.
The tables i have are
My top level clients table
id | name
1 | alan
2 | bob
3 | charlie
My 2nd level invoices table
id | client_id | paid
1 | 1 | 0
2 | 1 | 1
3 | 2 | 1
And my 3rd level services table
id | invoice_id | amount
1 | 1 | 5.00
2 | 1 | 10.00
3 | 2 | 5.00
4 | 3 | 10.00
And my desired outcome is
id | name | amount_owing | amount_paid
1 | alan | 15.00 | 5.00
2 | bob | null | 10.00
3 | charlie | null | null
I can get something that doesnt take into account whether they are paid or not
SELECT
clients.email,
jnt.*
FROM clients
LEFT JOIN (
SELECT
invoices.client_id,
SUM(amount) AS invoice_total_paid
FROM
invoices,
services
WHERE
invoices.id = services.invoice_id
GROUP BY
invoices.client_id
) AS jnt ON clients.id = jnt.client_id
which gives me
id | name | client_id | invoice_total
1 | alan | 1 | 20.00
2 | bob | 2 | 10.00
3 | charlie | 3 | null
but it doesn't divide up whether invoice was paid or not (and the subsequent paid and owing totals). Any help much appreciated
Upvotes: 1
Views: 52
Reputation: 3351
You need something like
...
SUM(IF(paid=1,amount,0)) AS amount_paid,
SUM(IF(paid=0,0,amount)) AS amount_owning
...
Upvotes: 0
Reputation: 72205
You can get the expected result using conditional aggregation:
SELECT c.id, c.name,
SUM(CASE WHEN i.paid = 0 THEN s.amount ELSE 0 END) AS amount_owing,
SUM(CASE WHEN i.paid = 1 THEN s.amount ELSE 0 END) AS amount_paid
FROM clients AS c
LEFT JOIN invoices AS i ON c.id = i.client_id
LEFT JOIN services AS s ON i.id = s.invoice_id
GROUP BY c.id, c.name
Upvotes: 3