MySQL

Calculate Running total without ordered index

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

•

Join Date: Jul 2008

Posts: 3

Reputation: scholzr is an unknown quantity at this point

Solved Threads: 0

scholzr

Offline

Newbie Poster

Calculate Running total without ordered index

Jul 27th, 2008

I am trying to find a way to calculate a running total of a field in my database, and while I have found a lot of good solutions, such as http://www.daniweb.com/forums/thread127644.html, they all require that I have a column which is ordered in the way that I want it to display. My problem, is that I do not have this. I am building a site where users can enter transactions on thier own, and they are not required to enter them in order. (see sample table below- the BALANCE column is what I am trying to calculate). I want to order them first by the date column, and then by the uniqueID if there are more than one for a given date.

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
Table acount
---------------------------------------------------------------------- | | |
uniqueID | date| amount| BALANCE
---------------------------------------------------------------------- | | |
DB10006 | 05-3-08 | 233.00 | 233.00
DB10007 | 05-3-08 | -33.00 | 200.00
DB10005 | 05-4-08 | 40.00 | 240.00
DB10004 | 05-5-08 | -20.00 | 220.00
----------------------------------------------------------------------------------
Table acount
---------------------------------------------------------------------- | | |
uniqueID | date| amount| BALANCE
---------------------------------------------------------------------- | | |
DB10006 | 05-3-08 | 233.00 | 233.00
DB10007 | 05-3-08 | -33.00 | 200.00
DB10005 | 05-4-08 | 40.00 | 240.00
DB10004 | 05-5-08 | -20.00 | 220.00
----------------------------------------------------------------------------------

I have found the following code:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
 SELECT uniqueID, DATE, amount,  amount + coalesce( (
 
SELECT sum( amount )
FROM acount b
WHERE b.uniqueID < a.uniqueID  ) , 0
) AS BALANCE
 
FROM acount a
ORDER BY uniqueID
 SELECT uniqueID, date, amount,  amount + coalesce( (

SELECT sum( amount )
FROM acount b
WHERE b.uniqueID < a.uniqueID  ) , 0
) AS BALANCE

FROM acount a
ORDER BY uniqueID

However, as I mentioned, I can not order the table by the uniqueID alone. I need to use the following order

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
ORDER BY DATE ASC, uniqueID ASC
ORDER BY date ASC, uniqueID ASC

Does anyone know how to incorporate this into the above query, or know another way to do this?

Last edited by scholzr; Jul 27th, 2008 at 11:38 am.

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: Calculate Running total without ordered index

Jul 27th, 2008

hello,

I am glad that you have got inspired by my solutions.

Questions:

1. How is your uniqueID computed?
2. Does uniqueID correspond to the date?
3. Isn't there a contradiction in

WHERE b.uniqueID < a.uniqueID

and your example

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
DB10006 | 05-3-08 | 233.00 | 233.00
DB10007 | 05-3-08 | -33.00 | 200.00
DB10005 | 05-4-08 | 40.00 | 240.00
DB10004 | 05-5-08 | -20.00 | 220.00
DB10006 | 05-3-08 | 233.00 | 233.00
DB10007 | 05-3-08 | -33.00 | 200.00
DB10005 | 05-4-08 | 40.00 | 240.00
DB10004 | 05-5-08 | -20.00 | 220.00

because smallest uniqueID DB10004 corresponds to largest date 05-5-08 ? Also, you should use ISO date: year-month-day, for example 2008-05-30.

-----
tesu

Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.

•

Join Date: Jul 2008

Posts: 3

Reputation: scholzr is an unknown quantity at this point

Solved Threads: 0

scholzr

Offline

Newbie Poster

Re: Calculate Running total without ordered index

Jul 27th, 2008

Thanks for the quick reply. First of all, I should note the table that I gave is only a sample of the table that I am actually using (actually adapted from the original post that I referenced). It was easier to just copy that and adapt it than try to explain everything in my table. I should have also noted that the sample table is ordered how I would like it to be ordered in the output.

•

Originally Posted by tesuji

1. How is your uniqueID computed?

My unique ID is actually an autonumbered integer which is assigned when the user inserts a new transaction (in my table it is an actual integer with no letters).

•

Originally Posted by tesuji

2. Does uniqueID correspond to the date?

No, the unique ID is a autonumber that is assigned in the order the transaction is inserted into the table. because the user can select the date that the transaction actually occured, these is not necessarily going to correspond.

•

Originally Posted by tesuji

3. Isn't there a contradiction in[

WHERE b.uniqueID < a.uniqueID

and your example
Help with Code Tags

MySQL Syntax (Toggle Plain Text)
DB10006 | 05-3-08 | 233.00 | 233.00
DB10007 | 05-3-08 | -33.00 | 200.00
DB10005 | 05-4-08 | 40.00 | 240.00
DB10004 | 05-5-08 | -20.00 | 220.00
DB10006 | 05-3-08 | 233.00 | 233.00 DB10007 | 05-3-08 | -33.00 | 200.00 DB10005 | 05-4-08 | 40.00 | 240.00 DB10004 | 05-5-08 | -20.00 | 220.00
because smallest uniqueID DB10004 corresponds to largest date 05-5-08 ?

In the example table that I gave yes. This is where my problem lies. The sample table is ordered how I want it to be. the example code is written as I found it. I am trying to figure out a way to adapt the code to allow me to obtain the results in the order of the sample table, but to do this, I need to somehow have

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
WHERE b.DATE < a.DATE AND b.uniqueID < a.uniqueID
WHERE b.date < a.date AND b.uniqueID < a.uniqueID

to match my necessary order. I just don't know if this is possible, and if so, how do I do it?

•

Originally Posted by tesuji

Also, you should use ISO date: year-month-day, for example 2008-05-30.

Yes, this was a mistake in entering the dates into the sample table. in my actual DB table, they are in the ISO format. (Sorry about that)

Last edited by scholzr; Jul 27th, 2008 at 12:06 pm.

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: Calculate Running total without ordered index

Jul 27th, 2008

Hello again,

The problem are duplicate date (fecha) values, why sum will be wrongly computed. To solve this, date and uniqueID must be combined in that order because date has higher priority than uniqueID (I think so). In the expression string(b.fecha, b.uniqueID) 1st, date and uniqueID will be converted to chars and 2nd, both chars will be concatenated, for example string(2008-03-05, '.', 10006) results in '2008-03-05.10006'. Now duplicates are impossible ! The following select statement

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT uniqueID, fecha as "Date", Amount,  amount + coalesce((SELECT sum(amount) FROM ozr b WHERE string(b.fecha, '.', b.uniqueID) < string(a.fecha, '.', a.uniqueID)   ), 0) as sAmount, string("Date", '.', uniqueID) as "test only" FROM ozr a ORDER BY string("Date", '.', uniqueID);
select uniqueID, fecha as "Date", Amount,  amount + coalesce((select sum(amount) from ozr b where string(b.fecha, '.', b.uniqueID) < string(a.fecha, '.', a.uniqueID)   ), 0) as sAmount, string("Date", '.', uniqueID) as "test only" from ozr a order by string("Date", '.', uniqueID);

gives the result you are asking for:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
uniqueID    DATE        Amount  sAmount    test only
----------------------------------------------------------
10006       2008-03-05  233.00  233.00      2008-03-05.10006
10007       2008-03-05  -33.00  200.00      2008-03-05.10007
10005       2008-04-05   40.00  240.00      2008-04-05.10005
10004       2008-05-05  -20.00  220.00      2008-05-05.10004
uniqueID    Date        Amount  sAmount    test only
----------------------------------------------------------
10006       2008-03-05  233.00  233.00      2008-03-05.10006
10007       2008-03-05  -33.00  200.00      2008-03-05.10007
10005       2008-04-05   40.00  240.00      2008-04-05.10005
10004       2008-05-05  -20.00  220.00      2008-05-05.10004

What you have to do is, you should find an appropriate function, like string(), what concatenates strings on your database, if MySql, CONCAT() might solve it. Possibly, you have to convert date type (b.fecha) and integer type (b.uniqueID) into varchars first. On my database system this task is done automatically.

-----
tesu

Last edited by tesuji; Jul 27th, 2008 at 3:37 pm.

•

Join Date: Jul 2008

Posts: 3

Reputation: scholzr is an unknown quantity at this point

Solved Threads: 0

scholzr

Offline

Newbie Poster

Re: Calculate Running total without ordered index

Jul 27th, 2008

Thanks! I finally got it figured out. Here's what I ended up doing (to get it to work in MySQL 5.0):

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT uniqueID, fecha as "Date", Amount,  amount + coalesce((SELECT sum(amount) FROM ozr b WHERE CONCAT(b.fecha, b.uniqueID) < CONCAT(a.fecha, a.uniqueID)   ), 0) as sAmount, CONCAT("Date", uniqueID) as "test only" FROM ozr a ORDER BY DATE, uniqueID;
select uniqueID, fecha as "Date", Amount,  amount + coalesce((select sum(amount) from ozr b where CONCAT(b.fecha, b.uniqueID) < CONCAT(a.fecha, a.uniqueID)   ), 0) as sAmount, CONCAT("Date", uniqueID) as "test only" from ozr a order by Date, uniqueID;