Calculate Running total without ordered index

[scholzr]

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.

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:

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

ORDER BY date ASC, uniqueID ASC

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

[tesuji]

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

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

[scholzr]

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.

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

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.

3. Isn't there a contradiction in[

WHERE b.uniqueID < a.uniqueID

and your example

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

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?

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)

[tesuji]

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

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:

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

[scholzr]

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

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;

[chaosprime]

You should mark the thread solved so people don't waste time on it.