SQL> SQL> -- The windowing clause 'range ....' or 'rows....' is valide only after SQL> -- the 'order by' clause. SQL> SQL> SQL> select * from t1_ order by c1,c2; C C2 - ---------- a 1 a 1 a 2 a 3 b 2 b 3 b 3 b 4 c 1 c 3 c 5 C C2 - ---------- d 3 d 7 d 7 d 7 d 8 16 rows selected. SQL> SQL> select c1,c2, sum(c2) over (partition by c1 order by c2) total from t1_; C C2 TOTAL - ---------- ---------- a 1 2 a 1 2 a 2 4 a 3 7 b 2 2 b 3 8 b 3 8 b 4 12 c 1 1 c 3 4 c 5 9 C C2 TOTAL - ---------- ---------- d 3 3 d 7 24 d 7 24 d 7 24 d 8 32 16 rows selected. SQL> SQL> -- Include the windowing clause SQL> -- UNBOUNDED PRECEDING means SQL> -- window starts at the first row of the partition. SQL> select c1,c2, sum(c2) over (partition by c1 order by c2 range between 2 unbounded preceding and current row) 3 total from t1_; C C2 TOTAL - ---------- ---------- a 1 2 a 1 2 a 2 4 a 3 7 b 2 2 b 3 8 b 3 8 b 4 12 c 1 1 c 3 4 c 5 9 C C2 TOTAL - ---------- ---------- d 3 3 d 7 24 d 7 24 d 7 24 d 8 32 16 rows selected. SQL> -- So what does 'CURRENT ROW' mean? SQL> -- As a 'end point' it means the window ends at the SQL> -- current row or value (depends on whether rows or range is used). SQL> -- In the previous query, it means the window starts at the SQL> -- first row of the partition (unbounded preceding) and ends at the SQL> -- current value(range) of within the partition (current row) SQL> -- SQL> -- SQL> -- SQL> select c1,c2, sum(c2) over (partition by c1 order by c2 range unbounded preceding) 2 total from t1_; C C2 TOTAL - ---------- ---------- a 1 2 a 1 2 a 2 4 a 3 7 b 2 2 b 3 8 b 3 8 b 4 12 c 1 1 c 3 4 c 5 9 C C2 TOTAL - ---------- ---------- d 3 3 d 7 24 d 7 24 d 7 24 d 8 32 16 rows selected. SQL> -- If 'between' is omitted and only one end point is specified, SQL> -- ORACLE considers it the start point with 'current row' as the end point. SQL> -- So this query is same as the previous one. SQL> -- SQL> -- SQL> -- SQL> -- UNBOUNDED FOLLOWING means SQL> -- window ends at the last row of the partition SQL> select c1,c2, sum(c2) over (partition by c1 order by c2 range between 2 unbounded preceding and unbounded following) 3 total from t1_; C C2 TOTAL - ---------- ---------- a 1 7 a 1 7 a 2 7 a 3 7 b 2 12 b 3 12 b 3 12 b 4 12 c 1 9 c 3 9 c 5 9 C C2 TOTAL - ---------- ---------- d 3 32 d 7 32 d 7 32 d 7 32 d 8 32 16 rows selected. SQL> -- So this query means apply sum() to all rows in within each window. SQL> -- The 'range' of the window is first row of the partition 'UNBOUNDED PRECEDING' SQL> -- and the last row of the partition (UNBOUNDED FOLLOWING). SQL> -- SQL> -- SQL> -- SQL> -- Window is defined as from the current value to the last row in the partition SQL> select c1,c2, sum(c2) over (partition by c1 order by c2 range between 2 current row and unbounded following) 3 total from t1_; C C2 TOTAL - ---------- ---------- a 1 7 a 1 7 a 2 5 a 3 3 b 2 12 b 3 10 b 3 10 b 4 4 c 1 9 c 3 8 c 5 5 C C2 TOTAL - ---------- ---------- d 3 32 d 7 29 d 7 29 d 7 29 d 8 8 16 rows selected. SQL> -- SQL> -- SQL> -- SQL> -- Same queries with 'ROWS' instead of 'RANGE' SQL> select c1,c2, sum(c2) over (partition by c1 order by c2 rows between 2 unbounded preceding and current row) 3 total from t1_; C C2 TOTAL - ---------- ---------- a 1 1 a 1 2 a 2 4 a 3 7 b 2 2 b 3 5 b 3 8 b 4 12 c 1 1 c 3 4 c 5 9 C C2 TOTAL - ---------- ---------- d 3 3 d 7 10 d 7 17 d 7 24 d 8 32 16 rows selected. SQL> SQL> select c1,c2, sum(c2) over (order by c2 rows unbounded preceding) sum_c2 from t1_; C C2 SUM_C2 - ---------- ---------- a 1 1 a 1 2 c 1 3 a 2 5 b 2 7 a 3 10 d 3 13 b 3 16 b 3 19 c 3 22 b 4 26 C C2 SUM_C2 - ---------- ---------- c 5 31 d 7 38 d 7 45 d 7 52 d 8 60 16 rows selected. SQL> SQL> select c1,c2, sum(c2) over (partition by c1 order by c2 rows between 2 unbounded preceding and unbounded following) 3 total from t1_; C C2 TOTAL - ---------- ---------- a 1 7 a 1 7 a 2 7 a 3 7 b 2 12 b 3 12 b 3 12 b 4 12 c 1 9 c 3 9 c 5 9 C C2 TOTAL - ---------- ---------- d 3 32 d 7 32 d 7 32 d 7 32 d 8 32 16 rows selected. SQL> SQL> select c1,c2, sum(c2) over (partition by c1 order by c2 rows between 2 current row and unbounded following) 3 total from t1_; C C2 TOTAL - ---------- ---------- a 1 7 a 1 6 a 2 5 a 3 3 b 2 12 b 3 10 b 3 7 b 4 4 c 1 9 c 3 8 c 5 5 C C2 TOTAL - ---------- ---------- d 3 32 d 7 29 d 7 22 d 7 15 d 8 8 16 rows selected. SQL> SQL> drop table t1_; Table dropped. SQL> SQL> spool off