Klaus Klaus - 1 year ago 88
SQL Question

Select sum and inner join

I have two tables

  • Bills
    id amount reference

  • Transactions
    id reference amount

The following SQL query

(SELECT SUM(amount)
FROM transactions
WHERE transactions.reference = bils.reference) AS paid
FROM bills
GROUP BY id HAVING paid<amount

was meant to some rows from table
, adding a column
with the sum of amount of related transactions.

However, it only works when there is at least one transaction for each bill. Otherwise, no line for a transaction-less bill is returned.

Probably, that's because I should have done an inner join!

So I try the following:

(SELECT SUM(transactions.amount)
FROM transactions
INNER JOIN bills ON transactions.reference = bills.reference) AS paid
FROM bills
HAVING paid < amount

However, this returns the same value of paid for all rows! What am I doing wrong ?

Answer Source

Use a left join instead of a subquery:

select b.id, b.amount, b.paid, sum(t.amount) as transactionamount
from bills b
left join transactions t on t.reference = b.reference
group by b.id, b.amount, b.paid
having b.paid < b.amount

To compare the sum of transactions to the amount, handle the null value that you get when there are no transactions:

having isnull(sum(t.amount), 0) < b.amount
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download