I need to get a debtors summary from the info in my database. What I need is the sum of amounts invoiced for each person from the invoice table, and the sum of amount paid from the payments table again by person.
What I have so far is: create temporary table debtors select people.surname, people.firstname, people.pid, sum(payments.amount as Paid), sum(invoice.totalexvat) as Invoiced from people, invoice, payments where people.pid=invoice.pid and people.pid=payments.pid group by people.pid; This doesn’t quite get me the right output unless of course a person only has one payment & invoice, I think I need to do a select within a select or something but can't quite work it out. The values I get are multiplied by the number of entries returned, ie if the total should be £50 and they have 5 entries then I get £250, am I missing a reference to distinct somewhere or just grouping it wrong? Can anybody help me in the right direction please. Paul Owen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
