All Forums Database
young 14 posts Joined 03/06
03 Apr 2006
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.00storeid 1002 prodid D sales 25000.00 Remaining Sum(sales) 45000.00storeid 1003 prodid A sales 30000.00 Remaining Sum(sales) 75000.00storeid 1001 prodid D sales 35000.00 Remaining Sum(sales) 110000.00storeid 1002 prodid C sales 35000.00 Remaining Sum(sales) 145000.00storeid 1002 prodid A sales 40000.00 Remaining Sum(sales) 185000.00storeid 1003 prodid D sales 50000.00 Remaining Sum(sales) 235000.00storeid 1001 prodid C sales 60000.00 Remaining Sum(sales) 295000.00storeid 1003 prodid B sales 65000.00 Remaining Sum(sales) 360000.00storeid 1001 prodid A sales 100000.00 Remaining Sum(sales) 460000.00storeid 1001 prodid F sales 150000.00 Remaining Sum(sales) 610000.00My question is I sort 'sales' desc, but asc in answer set, and what is difference between 'remaining sum(sales)' and 'Cumulative Sum(sales)'

Fred 1096 posts Joined 08/04
27 Apr 2006

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.

You must sign in to leave a comment.