Postgres: Sum from multiple joins resulting in values being more than expected I have 3 tables: invoices, invoice_payments, invoice_credit_notes. I want to fetch all those invoices where the total paid amount is less than the grand_total amount in invoices table.

An invoice can have multiple invoice_payments as well as multiple invoice_credit_notes.

invoice_payments has amount and discount field in it.

invoice_credit_notes has amount field in it.

Here's what I came up with:

SELECT invoices.* FROM invoices
LEFT OUTER JOIN invoice_payments ON invoice_payments.invoice_id = invoices.id 
LEFT OUTER JOIN invoice_credit_notes ON invoice_credit_notes.invoice_id = invoices.id 
GROUP BY invoices.id
HAVING ((COALESCE(SUM(invoice_payments.amount + invoice_payments.discount), 0) + COALESCE(SUM(invoice_credit_notes.amount), 0)) &lt; ROUND(invoices.grand_total, 0))


But there's an issue with my solution as there can be multiple invoice_payments or invoice_credit_notes the (COALESCE(SUM(invoice_payments.amount + invoice_payments.discount), 0) + COALESCE(SUM(invoice_credit_notes.amount), 0) is returning an amount that is higher than the actual paid amout. This is most probably because multiple tables are being joined.

So, how to overcome this issue?
Join against the results of the aggregation:

select inv.*
from invoices inv
  left join (
    select invoice_id, COALESCE(SUM(amount + discount), 0) as total_payments
    from invoice_payments
    group by invoice_id
  ) ip on ip.invoice_id = inv.id
  left join (
    select invoice_id, coalesce(sum(amount),0) as total_credit
    from invoice_credit_notes
    group by invoice_id
  ) icn on icn.invoice_id = inv.id
where coalesce(ip.total_payments,0) + coalesce(icn.total_credit,0) &lt; ROUND(inv.grand_total, 0))