Yesmin Patwary [EMAIL PROTECTED] wrote on 08/04/2005 12:42:44 PM:
Dear All,
I am trying to calculate balance for all id's. Can it be done using
only one MySQL query (without any scripting, eg: php,perl,asp..)?
price table:
id|price
1|150
2|100
3|300
payment table:
id|transaction_type|amount
1|PAID|75
1|PAID|25
1|REFUND|60
1|REFUND|30
1|PAID|140
2|PAID|35
2|REFUND|15
3|PAID|300
Thanks in advance for any help.
If by balance you mean (total paid) - (total refunded) for each account,
then yes, it is possible to calculate that value in one query:
SELECT id
, sum(if(transaction_type=PAID
,amount
,if(transaction_type=REFUND
, -amount
,0
)
)
) as balance
FROM paymenttable
GROUP BY id;
You could also write this same logic with a CASE statement
SELECT ID
, SUM(
CASE transaction_type
WHEN 'PAID' THEN amount
WHEN 'REFUND' THEN -amount
ELSE 0
END
) as balance
FROM paymenttable
GROUP BY ID;
Personally, I think the case statement is easier to read for this type of
query.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine