Klaus Klaus - 5 months ago 17
SQL Question

Select sum and inner join

I have two tables


  • Bills
    :
    id amount reference

  • Transactions
    :
    id reference amount



The following SQL query

SELECT
*,
(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
Bills
, adding a column
paid
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
*,
(SELECT SUM(transactions.amount)
FROM transactions
INNER JOIN bills ON transactions.reference = bills.reference) AS paid
FROM bills
GROUP BY id
HAVING paid < amount


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

Answer

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

Edit:
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