Select sum and inner join

Question

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 1

You need a RIGHT JOIN to include all bill rows.

EDIT So the final query will be

SELECT 
   *,
   (SELECT SUM(transactions.amount) 
    FROM transactions 
    WHERE transactions.reference = bills.reference) AS paid
FROM bills
WHERE paid < amount

Answer 2

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