-- Oracle Analytic Function - COUNT(*) OVER() and SUM(..) OVER()
-- The query below shows the use of COUNT(*) OVER (PARTITION BY ..) to 
-- count the number of products (prod_id) sold in a year
-- (100 -> 2, 101 -> 3, 102 -> 1, and so on).

-- The COUNT(*) OVER (ORDER BY qtr,prod_id) counts the data as specified
-- by the ORDER BY and is same as the ROWNUM.

-- The SUM(qty) OVER (PARTITION BY ..) sums the quantity of product for 
-- the year (100 -> 50+75, 101 -> 45+95+65, 102 -> 55 and so on).

-- The SUM(qty) OVER (ORDER BY qtr,prod_id) sums the cummulative total 
-- of quantity by quarter and product.

WITH q_quarterly AS
(SELECT  1 qtr,  100 prod_id, 50 qty FROM DUAL UNION
 SELECT  1 qtr,  101 prod_id, 45 qty FROM DUAL UNION
 SELECT  2 qtr,  100 prod_id, 75 qty FROM DUAL UNION
 SELECT  3 qtr,  101 prod_id, 95 qty FROM DUAL UNION
 SELECT  3 qtr,  103 prod_id, 85 qty FROM DUAL UNION
 SELECT  4 qtr,  101 prod_id, 65 qty FROM DUAL UNION
 SELECT  4 qtr,  102 prod_id, 55 qty FROM DUAL)
SELECT 
 COUNT(*) OVER (ORDER BY qtr,prod_id) cnt, -- same as ROWNUM
 qtr,  prod_id,
 COUNT(*) OVER (PARTITION BY prod_id) prod_cnts,
 SUM(qty) OVER (PARTITION BY prod_id) sum_qty,  qty,
 SUM(qty) OVER (ORDER BY qtr,prod_id) cumm_qty
FROM q_quarterly
ORDER BY qtr, prod_id;