Subject Re: [firebird-support] How to do a running total in SQL
Author Svein Erling Tysvær
This will become simpler with Firebird 3, which implements windowing functions.

Simple cases can also be possible with older versions and some imagination, e.g.

with tmp (Reference, SumDue) as
(Select  Reference, Sum(Due)
 From Invoices
 Group by 1)
Select Reference, SumDue, (select sum(SumDue) From tmp t2 where t1.Reference <= t2.Reference) RunningTotal
From tmp t1
Order by 1

Though sometimes this can be time consuming,

2015-10-23 8:40 GMT+02:00 Maya Opperman maya@... [firebird-support] <>:



I’d like to add a running total to my result set. For example:


Table: Invoices


Reference           Due

Invoice1               50.00

Invoice2               30.00

Invoice3               20.00


I’m guessing SQL would be something like:




Sum_Total(Due) as Balance

From Invoices


Desired Result:

Reference           Due                        Balance

Invoice1               50.00                     50.00

Invoice2               30.00                     80.00

Invoice3               20.00                     100.00


I know I can do this quite easily from within a selectable stored procedure, but the problem there is the running total won’t be correct if a different sort order is specified, which happens often in my real world application.


Is it possible to do this using just a simple SQL statement?