Thanks for the reply Philip!
Unfortunately that doesn't work. I should have clarified that there never
are null values in the 'amount' fields.
Maybe it is wrong to join both the payment and the order tables to the
account table in the query as neither of these directly related to
eachother.
On Thu, 16 Aug 2001, Rodney Moses wrote:
> This doesn't work, it has really wacky results:
> #balance of all accounts
> select a.id, (sum(o.amount) - sum(p.amount)) from
> accounts as a left join orders as o on a.id = o.account_id left join
> payments as p on a.id = p.account_id
> group by a.id;
>
> Can anyone tell me what I'm doing wrong? Is this sort of query possible?
Just a hunch, but the NULL columns might be screwing it up. If an account
has payments but no orders, then its sum of orders would be NULL (I think)
when you do the left join. NULL + anything = NULL.
Try replacing sum(o.amount) with ifnull(sum(o.amount),0) and the same for
p.amount; see if that helps. That should make it interpret NULL as 0.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
