RE: [firebird-support] How to do a running total in SQL

[Maya Opperman m...@omniaccounts.co.za [firebird-support]]

>>In Firebird 3 it will be possible with window functions. In Firebird 2.5 I 
>>don't know a way in pure SQL.

>>Mark

Great, thanks Mark.

Cool, so I’ll be doing something like this, when Firebird 3 is available:

select emp_no, salary,
sum(salary) over (order by salary) cum_salary,
sum(salary) over (order by salary desc) cum_salary_desc
from employee order by emp_no;

EMP_NO SALARY CUM_SALARY  CUM_SALARY_DESC
====  =
 2   105900.00  1990493.02  113637875.00
4   97500.001680929.02  113939039.00
28 22935.0022935.00115522468.02
121 9900.00 115522468.029900.00
145 32000.00113210.00   115441258.02
From: http://www.firebirdsql.org/file/community/ppts/fbcon11/fb3windowing.pdf

[firebird-support] How to do a running total in SQL

[Maya Opperman m...@omniaccounts.co.za [firebird-support]]

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   DueBalance
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

Re: [firebird-support] How to do a running total in SQL

[Svein Erling Tysvær setys...@gmail.com [firebird-support]]

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 m...@omniaccounts.co.za
[firebird-support] :

>
>
> 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   DueBalance
>
> 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
>
>
>
>
> 
>

Re: [firebird-support] How to do a running total in SQL

['Mark Rotteveel' m...@lawinegevaar.nl [firebird-support]]

In Firebird 3 it will be possible with window functions. In Firebird 2.5 I 
don't know a way in pure SQL.
Mark

- Reply message -
Van: "Maya Opperman m...@omniaccounts.co.za [firebird-support]" 

Aan: "firebird-support@yahoogroups.com" 
Onderwerp: [firebird-support] How to do a running total in SQL
Datum: vr, okt. 23, 2015 08:40

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   DueBalance
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