Cummulative sum...

[lbreddemann]

A colleague just reached out to me asking:

Good afternoon Lars,
Please, I need help to convert the oracle function "over( ORDER BY field ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)", because I didn't find the correct syntax and an example in SAP_HANA_sql_Reference.
I need to get the same result in Hana, as below:
Example:
select month, 
 sum(sale) as Total_Sale, 
 SUM(sum(sale)) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING AND CURRENT ROW) as Cumulative_sale
from all_sales 
where year = 2013
    and month between 1 and 3
group by month
order by month
Month Total_Sale     Cumulative_Sale
 01         2450    2450
 02         5000    7450
 03         1300    8750
Could you help me, please.
Thank you.

It's of course easy to refer to the documentation on window functions, where it is already stated that

"... The default window frame of the window function depends on whether or not a window orderby clause is specified.
If window orderby clause is specified, the default window frame becomes 'between UNBOUNDED PRECEDING and CURRENT ROW', 
that is, the window function computes on rows preceding or peer with current row..."

(just learned that myself recently...).

Therefore, I thought giving an example would be better.

So here we go. I just left out the YEAR column as it doesn't add anything to the example.

drop table all_sales;
create column table all_sales (month varchar(2), sale decimal (10,2));
insert into all_sales values ('01', 1000.0);
insert into all_sales values ('01', 450.0);
insert into all_sales values ('01', 1000.0);
insert into all_sales values ('02', 2500.0);
insert into all_sales values ('02', 2500.0);
insert into all_sales values ('03', 1300.0);
select   month, 
         sum(sale) as total_sales, 
         sum(sum(sale)) OVER (ORDER BY month ) as cumulative_sale
from all_sales 
where month between 1 and 3
group by month
order by month;

MONTH	TOTAL_SALES	CUMULATIVE_SALE
01	2450.00	2450.00
02	5000.00	7450.00
03	1300.00	8750.00

As you see, with window functions cumulative sums are easy as pie

- Lars

[former_member184768]

Thanks for sharing Lars.

, This should have been a blog or a bloglet (if there is any such term ).

Regards,

Ravi