I have tried many of the scripts that I found here but none did what I required.

I have 3 tables: `clients`

, `invoiced`

and `payments`

.

I would like to:

- sum all invoices per client within a date range
- sum all payments for clients with in the date range then subtract payments from invoiced to show amount outstanding.

I need to add the date range and get the total if not zero.

So far my query is

```
select id,
(select sum(Total) from invoiced where invoiced.ClientId = clients.Id) AS Invoiced,
(select sum(Amount) from payments where payments.ClientId = clients.Id) AS Paid
FROM clients
```

# Best How To :

The problem is the non existing relation between the invoice and the payments. This makes it a bit "dirtier" ;)

Try this:

```
select p.id, sum(i.total), sum(p.amount), sum(i.total) - sum(p.amount) as outstanding
from
(
select c.id, coalesce(pay.amount,0) as amount
from
(select ClientId, Sum(Amount) amount from payments group by ClientId) as pay
right join clients c on c.id = pay.ClientId
) p
left join
(
select c.id, inv.total
from
(select ClientId, Sum(Total) total from invoiced group by ClientId) as inv
left join clients c on c.id = inv.ClientId
) i
on p.id = i.id
group by id
having sum(i.total) <> sum(p.amount)
;
```

Updated **Sqlfiddle with results here**