How to find Balance?

2005-08-04 Thread Yesmin Patwary 

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.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com

Re: How to find Balance?

2005-08-04 Thread SGreen 
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

Re: How to find Balance?

2005-08-04 Thread Scott Noyes 
Is this what you had in mind? It assumes MySQL version 4.1 or later:

SELECT 
id, 
price - 
(SELECT 
SUM(IF(type = 'PAID', amount, 0 - amount)) 
FROM payment py 
WHERE py.id http://py.id = pr.id http://pr.id) 
AS balance 
FROM 
price pr;

On 8/4/05, Yesmin Patwary [EMAIL PROTECTED] wrote:
 
 
 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.
 
 
 __ 
 Do You Yahoo!?
 Tired of spam? Yahoo! Mail has the best spam protection around
 http://mail.yahoo.com