SQL Having sum(*) = sum(*)-CodePudding

I have a quick question about using a having clause.

I have two tables. 1 with invoices and 1 with payments. This is the select statement I want to use to give me not fully paid invoices:

select * 
from invoices i 
where not exists (select * 
                  from payments p 
                  having sum(p.amount) = sum(i.amount))

This is not working. Does someone know a solution?

CodePudding user response：

let's assume the invoices and payments table are joined on invoiceid, in that case you can join them and specify the condition for join

select i.invoiceid, i.amount,....(other information you want from invoices/payments)
from invoices i , payments p
where i.invoiceid = p.invoiceid
and p.amount <> i.amount

CodePudding user response：

You are thinking somewhat along the right lines, but you need to consider these questions:

Sum of payment amount by what?
Sum of invoice amount by what?
How are the invoice and payment tables related to each other?

Assuming that:

You have an invoice_id (or similar) in both tables
Each invoice_id in the invoices table can include multiple line items
Each invoice_id in the payments table can include multiple line items

Then, if you want to return all rows from the invoices table:

SELECT x.*,--get the invoice and all invoice line items
       y.invoice_amount AS total_invoice_payments_received --the current amount received
FROM (SELECT *,
             SUM(amount) OVER (PARTITION BY invoice_id) AS invoice_amount
      FROM invoices) x
  LEFT JOIN (SELECT invoice_id,
                    SUM(amount) AS invoice_amount
             FROM payments
             GROUP BY invoice_id) y ON x.invoice_id = y.invoice_id
WHERE x.invoice_amount <> COALESCE(y.invoice_amount,0); --in case there are no payment records

If there's only one line per invoice in the invoices table, then it's simpler:

SELECT x.*,--get the invoice and all invoice line items
       y.invoice_amount AS total_invoice_payments_received --the amount in payments
FROM invoices x
  LEFT JOIN (SELECT invoice_id,
                    SUM(amount) AS invoice_amount
             FROM payments
             GROUP BY invoice_id) y ON x.invoice_id = y.invoice_id
WHERE x.invoice_amount <> COALESCE(y.invoice_amount,0); --in case there are no payment records