What is the most efficient SQL statement to create running totals in Advantage SQL

Question

I would like to create a running total query statement in Advantage SQL and wonder what is the most efficient way. I am able to get the data with a query like this:

SELECT a.fiscal,sum(a.amount) periodTotal,(SELECT sum(b.amount) 
FROM tableName b  WHERE b.fiscal<=a.fiscal) runningTotal 
FROM tableName a GROUP BY a.fiscal,runningTotal

but the dataset is large (over 1 million records) and so I'm wondering if this is the most efficient way to get this info.

The raw data in the table is like so:

Fiscal Account
Period ID      Amount
====== ======= ======
     1 Cash         1
     1 Cash         2
     2 Cash         1
     2 Cash         2
     1 A/R          1
     1 A/R          2
     2 A/R          1
     2 A/R          2

I would like the results to be like so:

               Total  
Fiscal Account For    Running 
Period ID      Period Total
====== ======= ====== =======
     1 Cash         3       3
     2 Cash         3       6
     1 A/R          3       3
     2 A/R          3       6

Answer 1

Perhaps not the most efficient, but an SQL Cursor and iterate over the table?

Answer 2

The fastest way that I found is to use an intermediate table:

SELECT "Fiscal Period", "Account ID", Sum( amount ) PeriodTotal
INTO #temp
FROM TableName
GROUP BY 2, 1;

SELECT "Fiscal Period", "Account ID", PeriodTotal,
   ( SELECT Sum(PeriodTotal) FROM #temp b 
     WHERE b."Account ID" = a."Account ID" 
        And b."Fiscal Period" <= a."Fiscal Period" ) RunningTotal
FROM #temp a