Ad:

MySQL Discussion Thread
Unsolved
Views: 2588

Jul 27th, 2008

Calculate Running total without ordered index

Expand Post »

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.

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:

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

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.

scholzr

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Jul 2008

Permalink

Jul 27th, 2008

Re: Calculate Running total without ordered index

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

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

tesuji

Reputation Points: 158

Solved Threads: 98

Master Poster

Offline

720 posts
since Apr 2008

Permalink

Jul 27th, 2008

Re: Calculate Running total without ordered index

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.

Click to Expand / Collapse Quote 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).

Click to Expand / Collapse Quote 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.

Click to Expand / Collapse Quote originally posted by tesuji ...

3. Isn't there a contradiction in[

WHERE b.uniqueID < a.uniqueID

and your example
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

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?

Click to Expand / Collapse Quote 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.

scholzr

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Jul 2008

Permalink

Jul 27th, 2008

Re: Calculate Running total without ordered index

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

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:

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.

tesuji

Reputation Points: 158

Solved Threads: 98

Master Poster

Offline

720 posts
since Apr 2008

Permalink

Jul 27th, 2008

Re: Calculate Running total without ordered index

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

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;

Last edited by scholzr; Jul 27th, 2008 at 3:50 pm.

scholzr

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Jul 2008

Permalink

Jul 29th, 2008

Re: Calculate Running total without ordered index

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

chaosprime

Reputation Points: 18

Solved Threads: 4

Light Poster

Offline

46 posts
since Jul 2008

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.

Previous Thread in MySQL Forum Timeline: MySql Vs Oracle

Next Thread in MySQL Forum Timeline: setting up mysql db on new server

DaniWeb Message

Cancel Changes

User Name
Password