Subject Re: [firebird-support] How to do a running total in SQL
Author Svein Erling Tysvær
Post date 2015-10-23T07:08:20Z
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,
Set


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


Hi,

 

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:

Select

  Reference,

Due,

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?

 

Thanks

Maya