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
