TD olap func need help

Database
N/A

TD olap func need help

SELECT storeid
,prodid
,sales
,SUM(sales) OVER (ORDER BY sales DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM salestbl;

I got answer set:

storeid 1003 prodid C sales 20000.00 Remaining Sum(sales) 20000.00
storeid 1002 prodid D sales 25000.00 Remaining Sum(sales) 45000.00
storeid 1003 prodid A sales 30000.00 Remaining Sum(sales) 75000.00
storeid 1001 prodid D sales 35000.00 Remaining Sum(sales) 110000.00
storeid 1002 prodid C sales 35000.00 Remaining Sum(sales) 145000.00
storeid 1002 prodid A sales 40000.00 Remaining Sum(sales) 185000.00
storeid 1003 prodid D sales 50000.00 Remaining Sum(sales) 235000.00
storeid 1001 prodid C sales 60000.00 Remaining Sum(sales) 295000.00
storeid 1003 prodid B sales 65000.00 Remaining Sum(sales) 360000.00
storeid 1001 prodid A sales 100000.00 Remaining Sum(sales) 460000.00
storeid 1001 prodid F sales 150000.00 Remaining Sum(sales) 610000.00

My question is I sort 'sales' desc, but asc in answer set, and what is difference between 'remaining sum(sales)' and 'Cumulative Sum(sales)'
1 REPLY
Teradata Employee

Re: TD olap func need help

You specified an ORDER BY for purposes of calculating the windowed OLAP function, but if you want the result set ordered you need an ORDER BY clause at the end.

It's "Remaining" sum because you said UNBOUNDED FOLLOWING. "Cumulative" would be UNBOUNDED PRECEDING.