I'm missing something really silly in a query. I want to produce a list of accounts, the invoices and receipts and the balance against them.
Simply, there is an accounts table, invoices and receipts. I want to show the account details, sum(invoices), sum(receipts) and the balance. There is a situation where there maybe receipts and no invoices, and visa-versa, hence the if statement. SELECT account.accountId, account.name, if(count(invoices.invoiceId)=0,0,sum(invoices.amount)) as debits, if(count(receipts.receiptId)=0,0,sum(receipts.amount)) as credits, if(count(invoices.invoiceId)=0,0,sum(invoices.amount))-if(count(receipts.rec eiptId)=0,0,sum(receipts.amount)) as balance FROM family LEFT JOIN invoices ON invoices.accountId=account.accountId LEFT JOIN receipts ON receipts.accountId=account.accountId WHERE invoices.invoiceDate<=CURDATE() GROUP BY account.accountId For some reason, I'm getting results with multiple invoices etc against them - normally 3. Can anyone point out where I've gone wrong? Cheers Nunners -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
