SQL 2000 Query Statement for two tables in one call

By calling per customer (returns a line per customer) I wish to get the rows to show the Sum(BalanceRemaining), so if there is more then one invoice then it sums it up but doesn't return multiple invoices, I need it to return the list of customers.

So, CustomerID is stored in both tables to link up, many invoice to one customer rule.

If IsPaid = 0 and DueDate is in the future then return invoice but ideally I need the Sum of all the invoices returned as "BalanceRemaining" for that customer, as a customer list.




Start Free Trial

1:
2:
3:
4:
5:

SELECT     sos_customerlist.CustomerID, sos_customerlist.CompanyName, sos_invoices.BalanceRemaining, sos_invoices.AppliedAmount, 
                      sos_invoices.DueDate, sos_invoices.IsPaid
FROM         sos_customerlist INNER JOIN
                      sos_invoices ON sos_customerlist.CustomerID = sos_invoices.CustomerID
WHERE     (sos_invoices.IsPaid = 0) AND (sos_invoices.DueDate >= { fn NOW() })