943,865 Members | Top Members by Rank

Web Development > Databases > MySQL > how to calculate the value of one row to another
Ad:
  • MySQL Discussion Thread
  • Marked Solved
  • Views: 9182
  • MySQL RSS
You are currently viewing page 1 of this multi-page discussion thread
Permalink
Jun 4th, 2008
0

how to calculate the value of one row to another

Expand Post »
Hello friends,

I am new to this site, this is my first post, My question is..

How to calculate the one rows value for next row value?

sql Syntax (Toggle Plain Text)
  1. SELECT @rownum:=@rownum+1 "sl.no",
  2.  
  3.        						b.bus_activity_desc "Type",r.trn_no,
  4.  							o.Office_name,o.Address,o.Address2,o.Address3,
  5.             				DATE_FORMAT(r.trn_date,GET_FORMAT(DATE,'EUR')) as trn_date,
  6.             				l.Account_id ,
  7. 				          if(r.trn_type=6,r.total_trn_value,0) "Credit",
  8.                   if(r.trn_type=7,r.total_trn_value,0) "Debit",
  9.                   if(r.trn_type=6 ,@cbal:=(r.total_trn_value+@cbal),@cbal:=(@cbal-r.total_trn_value)) "Closing Balance"
  10.  
  11.  
  12.  
  13. 					FROM(SELECT @rownum:=0) s,
  14. 						office_master o,
  15. 						center_group_customer_master ce,
  16.      					receipt_payment_header r,
  17.      					saving_accounts l,
  18.      					(SELECT @cbal:=0) c ,
  19.      					accounts_summary_details a,
  20.      					business_activity_master b
  21. 					WHERE a.Account_id=l.savings_id AND
  22. 						  a.Customer_id=l.Customer_id AND
  23. 						  a.Product_id=2 AND
  24. 						  r.Trn_type IN (6,7) AND
  25. 						  r.Customer_id=l.Customer_id AND
  26. 						  r.Account_id=l.savings_id AND
  27. 						  b.Bus_activity_code=r.Trn_type AND
  28. 						  l.account_id='23' AND
  29. 						  r.trn_date BETWEEN '2006-06-01' AND '2008-06-31' AND
  30. 						  o.Office_id=ce.office_id AND
  31. 						  ce.customer_id=r.customer_id ORDER BY r.trn_no;

The above given is my query, don't search any mistakes in that, I am just asking the logic to calculate "closing balance" according to r.trn_no, please help me
Last edited by peter_budo; Jun 14th, 2008 at 2:29 pm. Reason: Keep It Organized - please use [code] tags
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
kutta_vin is offline Offline
14 posts
since Nov 2006
Permalink
Jun 4th, 2008
0

Re: how to calculate the value of one row to another

Welcome kutta_vin,

I really can't understand anything of your code and of that you are asking for. Perhaps you can give a small sample, for example on how the output of your select statement should look like (you can reduce it to the essential facts only).

krs,
tesu
Reputation Points: 158
Solved Threads: 98
Master Poster
tesuji is offline Offline
720 posts
since Apr 2008
Permalink
Jun 5th, 2008
0

Re: how to calculate the value of one row to another

Click to Expand / Collapse  Quote originally posted by tesuji ...
Welcome kutta_vin,

I really can't understand anything of your code and of that you are asking for. Perhaps you can give a small sample, for example on how the output of your select statement should look like (you can reduce it to the essential facts only).

krs,
tesu
Hai, Let me give some sample , for example..

I need the Mysql query to find the Balance calculations from credit and debit amount
of below noted table.But in the ACT table there is no field Balance column .
we must create and calculate the balance field at the run time.So,give me the solutions .


The output must looks like this.(i.e) for two values the output comes correctly like the following.
If credit means the balance must be added and if debit means the we must be subtract the debit
amount from balance.

Table Name : Account Transaction Ledger(ACT)
Table Fields : Transaction Id,Credit,Debit

------------------------------------------------------------------------------- | | |
TransactionID | CR | DR | BALANCE
--------------------------------------------------------------------------------- | | |
DB10004 | 233.00 | 0.00 | 233.00
DB10005 | 0.00 | 33.00| 200.00

----------------------------------------------------------------------------------

But if we got the another credit and debit in that table, then the problem arise, that is for example

------------------------------------------------------------------------------- | | |
TransactionID | CR | DR | BALANCE
--------------------------------------------------------------------------------- | | |
DB10004 | 233.00 | 0.00 | 233.00
DB10005 | 0.00 | 33.00 | 200.00
DB10006 | 40.00 | 0.00 | 240.00
DB10007 | 0.00 | 20.00 | 220.00

----------------------------------------------------------------------------------

As above given, i must get the result but i am not getting like that, instead of that all the credit amounts
first added and then the debit amounts are subtracted.(i.e) the actual result coming for me is

------------------------------------------------------------------------------- | | |
TransactionID | CR | DR | BALANCE
--------------------------------------------------------------------------------- | | |
DB10004 | 233.00 | 0.00 | 233.00
DB10006 | 40.00 | 0.00 | 273.00
DB10005 | 0.00 | 33.00 | 240.00
DB10007 | 0.00 | 20.00 | 220.00

----------------------------------------------------------------------------------

Even though I gave order by transactionId the result is coming like the following.
------------------------------------------------------------------------------- | | |
TransactionID | CR | DR | BALANCE
--------------------------------------------------------------------------------- | | |
DB10004 | 233.00 | 0.00 | 233.00
DB10005 | 0.00 | 33.00 | 273.00
DB10006 | 40.00 | 0.00 | 240.00
DB10006 | 0.00 | 20.00 | 220.00

----------------------------------------------------------------------------------

I must get the result as the second table, so please help me to find the solution.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
kutta_vin is offline Offline
14 posts
since Nov 2006
Permalink
Jun 5th, 2008
0

Re: how to calculate the value of one row to another

Click to Expand / Collapse  Quote originally posted by tesuji ...
Welcome kutta_vin,

I really can't understand anything of your code and of that you are asking for. Perhaps you can give a small sample, for example on how the output of your select statement should look like (you can reduce it to the essential facts only).

krs,
tesu
hello tesuji please help me.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
kutta_vin is offline Offline
14 posts
since Nov 2006
Permalink
Jun 5th, 2008
0

Re: how to calculate the value of one row to another

Hi kutta_vin

this is really a hard task. Finally I have got the following solution:
sql Syntax (Toggle Plain Text)
  1. SELECT transaction, credit, debit, credit+coalesce((SELECT sum(credit) 
  2.   FROM act b WHERE b.transaction < a.transaction),0) as sumCredit, 
  3.     debit+coalesce((SELECT sum(debit) FROM act b 
  4.       WHERE b.transaction < a.transaction),0) as sumDebit,  
  5.         sumCredit - sumDebit  as Balance  FROM act a ORDER BY transaction;
  6.  
  7. /********* result:
  8. transaction credit  debit   sumCredit   sumDebit    Balance
  9. -----------------------------------------------------------
  10.  DB10004    233.00  0.00    233.00      0.00        233.00
  11.  DB10005    0.00    33.00   233.00      33.00       200.00
  12.  DB10006    40.00   0.00    273.00      33.00       240.00
  13.  DB10007    0.00    20.00   273.00      53.00       220.00
  14. *********/

It is important that the rows be uniquely sequenced by Transaction column.

This is a modification of a well-known Celko-solution for running totals I have rewritten to meet your requirements. However, I haven't tested it completely, yet it seems that this solution meets at least your small sample. Tell me whether above solution is right to solve your problem.

krs,
tesu

p.s. Oh sorry, I really hope that MySQL would also be able to deal with subselects as specified in SQL standard 1999!
Last edited by tesuji; Jun 5th, 2008 at 9:10 pm.
Reputation Points: 158
Solved Threads: 98
Master Poster
tesuji is offline Offline
720 posts
since Apr 2008
Permalink
Jun 6th, 2008
0

Re: how to calculate the value of one row to another

Hai tesuji,

Thank U for your reply. sorry i have missed to mention one thing.....
that is ,
In My ACT table credit and debit fields are not a field name . it is a alias name of total_transaction_value according to the transaction type that is deposit or withdrawal i have calculate the debit and credit . that is if the transaction_type is DEPOSIT then the total_transaction_value(Credit alias for total_transaction_value ) is fetched from ACT table thats alias name is CREDIT. If the transaction_type is WITHDRAWAL then the total_transaction_value(Debit Alias for total_transaction_value) is fetched from ACT table thats alias name is DEBIT .

Actually my table(ACT) is look like this .,


Table Column name : AccountId
TransactionID
Transaction_type
Total_transaction_value

The Actual table is looks like this,
-------------------------------------------------------------------------------------------------
Account_id | TransactionId | Transaction_type | total_transaction_value
-------------------------------------------------------------------------------------------------
SA001 | DB10004 | Deposit | 233.00
SA001 | DB10005 | Withdrawal | 33.00
SA001 | DB10006 | Deposit | 40.00
SA001 | DB10007 | Withdrawal | 20.00
-------------------------------------------------------------------------------------------------

TransactionId is unique for each transactions(deposit/withdrawal).

From this table columns name we have to show the output like what i told before.

that is .,

-------------------------------------------------------------------------------
TransactionID | CR | DR | BALANCE
-------------------------------------------------------------------------------
DB10004 | 233.00 | 0.00 | 233.00
DB10005 | 0.00 | 33.00 | 200.00
DB10006 | 40.00 | 0.00 | 240.00
DB10007 | 0.00 | 20.00 | 220.00


here the above table(ACT) ., CR and DR is only the alias name of total_transaction_value.
it is seperated only by of transaction_type.


Kindly give me the solutions for this requirement.

regards,

kutta_vin
Reputation Points: 10
Solved Threads: 0
Newbie Poster
kutta_vin is offline Offline
14 posts
since Nov 2006
Permalink
Jun 6th, 2008
1

Re: how to calculate the value of one row to another

Hello kutta_vin,

well, "forgotten to mention" has created some superfluous work. However it is rather simple to get the same result from your modified tables. Supposing a table act2(AccountId, TransactionID, trtype, trvalue) exists, then there are some possibilities to solve your problem. All solutions that come into consideration are based on new CASE clause (MySql supports it). Then, one can solve the problem by means of 1) stored procedures, 2) SQL views, or 3) WITH clause (MySql 5 has it).

I have used the WITH clause as you can see. With WITH clause first a temporary table tact which selects from your new table structure act2 the old structure with columns transaction, credit and debit. On that temporary table my yesterday posted SQL select can be executed without any changes. Nice, isn't it?
sql Syntax (Toggle Plain Text)
  1. with tact (transaction, credit, debit) as
  2. (
  3.    SELECT TransactionID, 
  4.      CASE trtype
  5.           WHEN 'Deposit' THEN coalesce(trvalue, 0)
  6.           ELSE 0
  7.      END as Credit,
  8.      CASE trtype
  9.           WHEN 'Withdrawal' THEN coalesce(trvalue, 0)
  10.           ELSE 0
  11.      END as Debit
  12.    FROM act2 WHERE accountID = 'SA001'
  13. ) 
  14. SELECT transaction, credit, debit, credit+coalesce((SELECT sum(credit) FROM tact b
  15.   WHERE b.transaction < a.transaction), 0) as sumCredit, debit+coalesce((SELECT sum(debit) FROM tact b
  16.     WHERE b.transaction < a.transaction), 0) as sumDebit, sumCredit - sumDebit  as Balance
  17.        FROM tact a ORDER BY transaction;
  18.  
  19. /********* result:
  20. transaction credit  debit   sumCredit   sumDebit    Balance
  21. -----------------------------------------------------------
  22.  DB10004    233.00  0.00    233.00      0.00        233.00
  23.  DB10005    0.00    33.00   233.00      33.00       200.00
  24.  DB10006    40.00   0.00    273.00      33.00       240.00
  25.  DB10007    0.00    20.00   273.00      53.00       220.00
  26. *********/

I hope this code helps you.

krs,
tesu
Reputation Points: 158
Solved Threads: 98
Master Poster
tesuji is offline Offline
720 posts
since Apr 2008
Permalink
Jun 17th, 2008
0

Re: how to calculate the value of one row to another

Hi tesuji,

the problem solved for me with your old query idea, that is with the help of

(SELECT transaction, credit, debit, credit+coalesce((SELECT sum(credit)

FROM act b WHERE b.transaction < a.transaction),0) AS sumCredit,

debit+coalesce((SELECT sum(debit) FROM act b

WHERE b.transaction < a.transaction),0) AS sumDebit,

sumCredit - sumDebit AS Balance FROM act a ORDER BY transaction; )

this query. But the alias names sumCredit - sumDebit cannot be evaluated with only alias names , i used the same condition for that ,( credit+coalesce((SELECT sum(credit)
FROM act b WHERE b.transaction < a.transaction),0))-(debit+coalesce((SELECT sum(debit) FROM act bWHERE b.transaction < a.transaction),0)).
Reputation Points: 10
Solved Threads: 0
Newbie Poster
kutta_vin is offline Offline
14 posts
since Nov 2006
Permalink
Dec 10th, 2008
0

Re: how to calculate the value of one row to another

Click to Expand / Collapse  Quote originally posted by tesuji ...
Hello kutta_vin,

well, "forgotten to mention" has created some superfluous work. However it is rather simple to get the same result from your modified tables. Supposing a table act2(AccountId, TransactionID, trtype, trvalue) exists, then there are some possibilities to solve your problem. All solutions that come into consideration are based on new CASE clause (MySql supports it). Then, one can solve the problem by means of 1) stored procedures, 2) SQL views, or 3) WITH clause (MySql 5 has it).

I have used the WITH clause as you can see. With WITH clause first a temporary table tact which selects from your new table structure act2 the old structure with columns transaction, credit and debit. On that temporary table my yesterday posted SQL select can be executed without any changes. Nice, isn't it?
sql Syntax (Toggle Plain Text)
  1. with tact (transaction, credit, debit) as
  2. (
  3.    SELECT TransactionID, 
  4.      CASE trtype
  5.           WHEN 'Deposit' THEN coalesce(trvalue, 0)
  6.           ELSE 0
  7.      END as Credit,
  8.      CASE trtype
  9.           WHEN 'Withdrawal' THEN coalesce(trvalue, 0)
  10.           ELSE 0
  11.      END as Debit
  12.    FROM act2 WHERE accountID = 'SA001'
  13. ) 
  14. SELECT transaction, credit, debit, credit+coalesce((SELECT sum(credit) FROM tact b
  15.   WHERE b.transaction < a.transaction), 0) as sumCredit, debit+coalesce((SELECT sum(debit) FROM tact b
  16.     WHERE b.transaction < a.transaction), 0) as sumDebit, sumCredit - sumDebit  as Balance
  17.        FROM tact a ORDER BY transaction;
  18.  
  19. /********* result:
  20. transaction credit  debit   sumCredit   sumDebit    Balance
  21. -----------------------------------------------------------
  22.  DB10004    233.00  0.00    233.00      0.00        233.00
  23.  DB10005    0.00    33.00   233.00      33.00       200.00
  24.  DB10006    40.00   0.00    273.00      33.00       240.00
  25.  DB10007    0.00    20.00   273.00      53.00       220.00
  26. *********/

I hope this code helps you.

krs,
tesu
The 'balance' column returns nothing. Whats wrong with the code?
Reputation Points: 10
Solved Threads: 0
Newbie Poster
master_m is offline Offline
4 posts
since Nov 2008
Permalink
Jun 24th, 2009
0

Re: how to calculate the value of one row to another

Hello Tesu,

This is really helpful for my requirement. I am using the query

sql Syntax (Toggle Plain Text)
  1. SELECT id, mill_rate, mill_rate+coalesce((SELECT sum(mill_rate) 
  2.   FROM mdata b WHERE b.id < a.id ),0) as sumMill
  3.  FROM mdata a 
  4. WHERE a.fiscalyear=2008
  5. AND  a.plant_number='xx'
  6.  ORDER BY a.id;

but it sum the entire mdata mill_rate column but I want to sum only based on fiscalyear=2008 and plantnumber=xx. i tried like this in the subquery to include fiscalyear but it is not allowing. I am using oracle.

sql Syntax (Toggle Plain Text)
  1. SELECT id, mill_rate, mill_rate+coalesce((SELECT sum(mill_rate) 
  2.   FROM mdata b WHERE b.id < a.id ),0 AND b.fiscalyear='2008') as sumMill
  3.  FROM mdata a 
  4.  WHERE a.fiscalyear=2008
  5. AND  a.plant_number='xx'
  6. ORDER BY a.id;

Please advise.

Regards
Selva
Last edited by peter_budo; Jun 25th, 2009 at 2:25 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
kselva is offline Offline
2 posts
since Jun 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in MySQL Forum Timeline: SQL problem
Next Thread in MySQL Forum Timeline: Using data from two different tables





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC