SUM(...) OVER (PARTITION BY ... ORDER BY ... RANGE UNBOUNDED PRECEDING)
The query below shows the difference between running total (rt_sum) and (rt_csum) by the use of RANGE UNBOUNDED PRECEDING. When the value of two consecutive partitioned values is the same (9500 in query below), then both will have the same total value (RT_CSum) as shown in row 4 and 5 (Central/West region) for the year 2011 in the query below.

SELECT 
 year_id, region, sales,
 SUM(sales) OVER
     (PARTITION BY year_id ORDER BY sales, region) 
      rt_sales,
 SUM(sales) OVER
     (PARTITION BY year_id ORDER BY sales
      RANGE UNBOUNDED PRECEDING) rt_csum
FROM 
(SELECT  2011 year_id, 'East'    region,  7500 sales
 FROM DUAL
 UNION 
 SELECT  2011 year_id, 'North'   region,  6500 sales
 FROM DUAL
 UNION 
 SELECT  2011 year_id, 'South'   region,  8500 sales
 FROM DUAL
 UNION 
 SELECT  2011 year_id, 'West'    region,  9500 sales
 FROM DUAL
 UNION 
 SELECT  2011 year_id, 'Central' region,  9500 sales
 FROM DUAL
 UNION 
 SELECT  2012 year_id, 'North'   region,  9250 sales
 FROM DUAL
 UNION 
 SELECT  2012 year_id, 'South'   region,  8550 sales
 FROM DUAL
) q_sales;
Sum() OVER Query Output

Oracle 11gXEr2 - SUM() OVER

Oracle registered trademark of Oracle Corporation.

Last Revised on: August 31st, 2014

  341