Kayshav.com
About Developer Oracle 11g Technology Information Sitemap

Oracle Analytic Function SUM() OVER()
-- The example below shows the difference between 
-- SUM(sales) OVER (PARTITION BY year_id ORDER BY sales 
--      RANGE UNBOUNDED PRECEDING)
-- SUM(sales) OVER (PARTITION BY year_id ORDER BY sales
--      RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

SELECT   year_id, region, sales,
 SUM(sales) OVER
     (PARTITION BY year_id ORDER BY sales, region)  cumm_yearly,
 SUM(sales) OVER (PARTITION BY year_id ORDER BY sales
      RANGE UNBOUNDED PRECEDING) preceding_sum,
 SUM(sales) OVER (PARTITION BY year_id ORDER BY sales
      RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)  following_sum
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;

Oracle 11g SUM() OVER() output

-- Using RANGE UNBOUNDED PRECEDING, the cummulative sum increases in
-- ascending order. Based on the windowing clause, if two consecutive 
-- values are the same (9500 for year 2011, West and Central), the 
-- preceding_sum is the same (41500).

-- Using RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, the sum_following
-- starts with total and deceases with corresponding row value. For same 
-- values in consecutive rows, the sum_following remains the same (19000).


Oracle 11gXEr2 - Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: August 31, 2014

  23403