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?