I know this is late, but it does answer your original question.
/*Read the comments the same way that SQL runs the query
1) FROM
2) GROUP
3) SELECT
4) My final notes at the bottom
*/
SELECT
list.invoiceid
, cust.customernumber
, MAX(list.inv_amount) AS invoice_amount/* we select the max because it will be the same for each payment to that invoice (presumably invoice amounts do not vary based on payment) */
, MAX(list.inv_amount) - SUM(list.pay_amount) AS [amount_due]
FROM
Customers AS cust
INNER JOIN
Payments AS pay
ON
pay.customerid = cust.customerid
INNER JOIN ( /* generate a list of payment_ids, their amounts, and the totals of the invoices they billed to*/
SELECT
inpay.paymentid AS paymentid
, inv.invoiceid AS invoiceid
, inv.amount AS inv_amount
, pay.amount AS pay_amount
FROM
InvoicePayments AS inpay
INNER JOIN
Invoices AS inv
ON inv.invoiceid = inpay.invoiceid
INNER JOIN
Payments AS pay
ON pay.paymentid = inpay.paymentid
) AS list
ON
list.paymentid = pay.paymentid
/* so at this point my result set would look like:
-- All my customers (crossed by) every paymentid they are associated to (I'll call this A)
-- Every invoice payment and its association to: its own ammount, the total invoice ammount, its own paymentid (what I call list)
-- Filter out all records in A that do not have a paymentid matching in (list)
-- we filter the result because there may be payments that did not go towards invoices!
*/
GROUP BY
/* we want a record line for each customer and invoice ( or basically each invoice but i believe this makes more sense logically */
cust.customernumber
, list.invoiceid
/*
-- we can improve this query by only hitting the Payments table once by moving it inside of our list subquery,
-- but this is what made sense to me when I was planning.
-- Hopefully it makes it clearer how the thought process works to leave it in there
-- as several people have already pointed out, the data structure of the DB prevents us from looking at customers with invoices that have no payments towards them.
*/