Hi,
This is probably not the right forum for this question, sorry, I'm really
stuck...
I need to write a query for an e-commerce system that calculates account
balances. With the current database structure I can write two separate
queries, one to get the sum of all payments and one to get the sum of all
orders, but I cannot work out how to write a single query that merges these
results, returning the balance of all accounts. I'm wondering if this sort
of query would be possible?
The table structures are:
table accounts{
id bigint(20) default NULL,
}
table payments{
amount float default NULL,
account_id bigint default NULL,
}
table orders{
amount float default NULL,
account_id bigint default NULL,
}
These work:
#sum of all orders
select a.id, sum(o.amount) from
accounts as a left join orders as o on a.id = o.account_id
group by a.id;
#sum of all payments
select a.id, sum(p.amount) from
accounts as a left join payments as p on a.id = p.account_id
group by a.id;
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?
-Rodney
---------------------------------------------------------------------
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
