MySQL

how to calculate the value of one row to another

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Nov 2006

Posts: 14

Reputation: kutta_vin is an unknown quantity at this point

Solved Threads: 0

kutta_vin

Offline

Newbie Poster

how to calculate the value of one row to another

Jun 4th, 2008

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?

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT @rownum:=@rownum+1 "sl.no",
 
       						b.bus_activity_desc "Type",r.trn_no,
 							o.Office_name,o.Address,o.Address2,o.Address3,
            				DATE_FORMAT(r.trn_date,GET_FORMAT(DATE,'EUR')) as trn_date,
            				l.Account_id ,
				          if(r.trn_type=6,r.total_trn_value,0) "Credit",
                  if(r.trn_type=7,r.total_trn_value,0) "Debit",
                  if(r.trn_type=6 ,@cbal:=(r.total_trn_value+@cbal),@cbal:=(@cbal-r.total_trn_value)) "Closing Balance"
 
 
 
					FROM(SELECT @rownum:=0) s,
						office_master o,
						center_group_customer_master ce,
     					receipt_payment_header r,
     					saving_accounts l,
     					(SELECT @cbal:=0) c ,
     					accounts_summary_details a,
     					business_activity_master b
					WHERE a.Account_id=l.savings_id AND
						  a.Customer_id=l.Customer_id AND
						  a.Product_id=2 AND
						  r.Trn_type IN (6,7) AND
						  r.Customer_id=l.Customer_id AND
						  r.Account_id=l.savings_id AND
						  b.Bus_activity_code=r.Trn_type AND
						  l.account_id='23' AND
						  r.trn_date BETWEEN '2006-06-01' AND '2008-06-31' AND
						  o.Office_id=ce.office_id AND
						  ce.customer_id=r.customer_id ORDER BY r.trn_no;
select @rownum:=@rownum+1 "sl.no",

       						b.bus_activity_desc "Type",r.trn_no,
 							o.Office_name,o.Address,o.Address2,o.Address3,
            				DATE_FORMAT(r.trn_date,GET_FORMAT(DATE,'EUR')) as trn_date,
            				l.Account_id ,
				          if(r.trn_type=6,r.total_trn_value,0) "Credit",
                  if(r.trn_type=7,r.total_trn_value,0) "Debit",
                  if(r.trn_type=6 ,@cbal:=(r.total_trn_value+@cbal),@cbal:=(@cbal-r.total_trn_value)) "Closing Balance"



					from(select @rownum:=0) s,
						office_master o,
						center_group_customer_master ce,
     					receipt_payment_header r,
     					saving_accounts l,
     					(select @cbal:=0) c ,
     					accounts_summary_details a,
     					business_activity_master b
					where a.Account_id=l.savings_id and
						  a.Customer_id=l.Customer_id and
						  a.Product_id=2 and
						  r.Trn_type in (6,7) and
						  r.Customer_id=l.Customer_id and
						  r.Account_id=l.savings_id and
						  b.Bus_activity_code=r.Trn_type and
						  l.account_id='23' and
						  r.trn_date between '2006-06-01' and '2008-06-31' and
						  o.Office_id=ce.office_id and
						  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

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

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

Jun 4th, 2008

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

•

Join Date: Nov 2006

Posts: 14

Reputation: kutta_vin is an unknown quantity at this point

Solved Threads: 0

kutta_vin

Offline

Newbie Poster

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

Jun 5th, 2008

•

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.

•

Join Date: Nov 2006

Posts: 14

Reputation: kutta_vin is an unknown quantity at this point

Solved Threads: 0

kutta_vin

Offline

Newbie Poster

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

Jun 5th, 2008

•

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.

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

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

Jun 5th, 2008

Hi kutta_vin

this is really a hard task. Finally I have got the following solution:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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;
 
/********* result:
transaction credit  debit   sumCredit   sumDebit    Balance
-----------------------------------------------------------
 DB10004    233.00  0.00    233.00      0.00        233.00
 DB10005    0.00    33.00   233.00      33.00       200.00
 DB10006    40.00   0.00    273.00      33.00       240.00
 DB10007    0.00    20.00   273.00      53.00       220.00
*********/
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;

/********* result:
transaction credit  debit   sumCredit   sumDebit    Balance
-----------------------------------------------------------
 DB10004    233.00  0.00    233.00      0.00        233.00
 DB10005    0.00    33.00   233.00      33.00       200.00
 DB10006    40.00   0.00    273.00      33.00       240.00
 DB10007    0.00    20.00   273.00      53.00       220.00
*********/

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.

•

Join Date: Nov 2006

Posts: 14

Reputation: kutta_vin is an unknown quantity at this point

Solved Threads: 0

kutta_vin

Offline

Newbie Poster

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

Jun 6th, 2008

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

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

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

Jun 6th, 2008

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?

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
with tact (transaction, credit, debit) as
(
   SELECT TransactionID, 
     CASE trtype
          WHEN 'Deposit' THEN coalesce(trvalue, 0)
          ELSE 0
     END as Credit,
     CASE trtype
          WHEN 'Withdrawal' THEN coalesce(trvalue, 0)
          ELSE 0
     END as Debit
   FROM act2 WHERE accountID = 'SA001'
) 
SELECT transaction, credit, debit, credit+coalesce((SELECT sum(credit) FROM tact b
  WHERE b.transaction < a.transaction), 0) as sumCredit, debit+coalesce((SELECT sum(debit) FROM tact b
    WHERE b.transaction < a.transaction), 0) as sumDebit, sumCredit - sumDebit  as Balance
       FROM tact a ORDER BY transaction;
 
/********* result:
transaction credit  debit   sumCredit   sumDebit    Balance
-----------------------------------------------------------
 DB10004    233.00  0.00    233.00      0.00        233.00
 DB10005    0.00    33.00   233.00      33.00       200.00
 DB10006    40.00   0.00    273.00      33.00       240.00
 DB10007    0.00    20.00   273.00      53.00       220.00
*********/
with tact (transaction, credit, debit) as
(
   select TransactionID, 
     case trtype
          when 'Deposit' then coalesce(trvalue, 0)
          else 0
     end as Credit,
     case trtype
          when 'Withdrawal' then coalesce(trvalue, 0)
          else 0
     end as Debit
   from act2 where accountID = 'SA001'
) 
select transaction, credit, debit, credit+coalesce((select sum(credit) from tact b
  where b.transaction < a.transaction), 0) as sumCredit, debit+coalesce((select sum(debit) from tact b
    where b.transaction < a.transaction), 0) as sumDebit, sumCredit - sumDebit  as Balance
       from tact a order by transaction;

/********* result:
transaction credit  debit   sumCredit   sumDebit    Balance
-----------------------------------------------------------
 DB10004    233.00  0.00    233.00      0.00        233.00
 DB10005    0.00    33.00   233.00      33.00       200.00
 DB10006    40.00   0.00    273.00      33.00       240.00
 DB10007    0.00    20.00   273.00      53.00       220.00
*********/

I hope this code helps you.

krs,
tesu

•

Join Date: Nov 2006

Posts: 14

Reputation: kutta_vin is an unknown quantity at this point

Solved Threads: 0

kutta_vin

Offline

Newbie Poster

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

Jun 17th, 2008

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)).

•

Join Date: Nov 2008

Posts: 4

Reputation: master_m is an unknown quantity at this point

Solved Threads: 0

master_m

Offline

Newbie Poster

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

Dec 10th, 2008

•

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?
Help with Code Tags

sql Syntax (Toggle Plain Text)
with tact (transaction, credit, debit) as
(
   SELECT TransactionID, 
     CASE trtype
          WHEN 'Deposit' THEN coalesce(trvalue, 0)
          ELSE 0
     END as Credit,
     CASE trtype
          WHEN 'Withdrawal' THEN coalesce(trvalue, 0)
          ELSE 0
     END as Debit
   FROM act2 WHERE accountID = 'SA001'
) 
SELECT transaction, credit, debit, credit+coalesce((SELECT sum(credit) FROM tact b
  WHERE b.transaction < a.transaction), 0) as sumCredit, debit+coalesce((SELECT sum(debit) FROM tact b
    WHERE b.transaction < a.transaction), 0) as sumDebit, sumCredit - sumDebit  as Balance
       FROM tact a ORDER BY transaction;
 
/********* result:
transaction credit  debit   sumCredit   sumDebit    Balance
-----------------------------------------------------------
 DB10004    233.00  0.00    233.00      0.00        233.00
 DB10005    0.00    33.00   233.00      33.00       200.00
 DB10006    40.00   0.00    273.00      33.00       240.00
 DB10007    0.00    20.00   273.00      53.00       220.00
*********/
with tact (transaction, credit, debit) as ( select TransactionID, case trtype when 'Deposit' then coalesce(trvalue, 0) else 0 end as Credit, case trtype when 'Withdrawal' then coalesce(trvalue, 0) else 0 end as Debit from act2 where accountID = 'SA001' ) select transaction, credit, debit, credit+coalesce((select sum(credit) from tact b where b.transaction < a.transaction), 0) as sumCredit, debit+coalesce((select sum(debit) from tact b where b.transaction < a.transaction), 0) as sumDebit, sumCredit - sumDebit as Balance from tact a order by transaction; /********* result: transaction credit debit sumCredit sumDebit Balance ----------------------------------------------------------- DB10004 233.00 0.00 233.00 0.00 233.00 DB10005 0.00 33.00 233.00 33.00 200.00 DB10006 40.00 0.00 273.00 33.00 240.00 DB10007 0.00 20.00 273.00 53.00 220.00 *********/
I hope this code helps you.

krs,
tesu

The 'balance' column returns nothing. Whats wrong with the code?

•

Join Date: Jun 2009

Posts: 2

Reputation: kselva is an unknown quantity at this point

Solved Threads: 0

kselva

Offline

Newbie Poster

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

#10

Jun 24th, 2009

Hello Tesu,

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT id, mill_rate, mill_rate+coalesce((SELECT sum(mill_rate) 
  FROM mdata b WHERE b.id < a.id ),0) as sumMill
 FROM mdata a 
WHERE a.fiscalyear=2008
AND  a.plant_number='xx'
 ORDER BY a.id;
select id, mill_rate, mill_rate+coalesce((select sum(mill_rate) 
  from mdata b where b.id < a.id ),0) as sumMill
 from mdata a 
where a.fiscalyear=2008
and  a.plant_number='xx'
 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.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT id, mill_rate, mill_rate+coalesce((SELECT sum(mill_rate) 
  FROM mdata b WHERE b.id < a.id ),0 AND b.fiscalyear='2008') as sumMill
 FROM mdata a 
 WHERE a.fiscalyear=2008
AND  a.plant_number='xx'
ORDER BY a.id;
select id, mill_rate, mill_rate+coalesce((select sum(mill_rate) 
  from mdata b where b.id < a.id ),0 and b.fiscalyear='2008') as sumMill
 from mdata a 
 where a.fiscalyear=2008
and  a.plant_number='xx'
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.