RunningSum / Cumulative

[1974maiden]

Select
CUR_TRN_DT,
sum(F_LT_AMOUNT),
sum(F_LT_AMOUNT) over (order by CUR_TRN_DT)
FROM FAT_BSE_LEDGER_TRANS
where
LT_LEDGER_TYPE_ID='AA' and
CUR_TRN_DT>= '01/01/2007' and
GL_OBJ_ACCOUNT_DESC='22895'
group by CUR_TRN_DT

I'm trying to do a runningsum but when I run the code it's failing with expression 'F_LT_AMOUNT' is not specified in the Group By etc ...

Any help is appreciated !

 

[blom0344]

I think the trouble is that you mix an aggregate (sum(F_LT_AMOUNT)) with an OLAP function that effectively should account for the running sum.

It should be:

Select 
CUR_TRN_DT, 
F_LT_AMOUNT,
sum(F_LT_AMOUNT) over (order by CUR_TRN_DT)
FROM FAT_BSE_LEDGER_TRANS
where
LT_LEDGER_TYPE_ID='AA' and  
CUR_TRN_DT>=  '01/01/2007' and
GL_OBJ_ACCOUNT_DESC='22895'
order by CUR_TRN_DT

or (2nd scenario) , perform the aggregation first in an inline view.
(And apply the OLAP function on the aggregated set)


Ties Blom