OVER (PARTITION BY …. ORDER BY…. ) allows values to be partitioned based on the list of expressions
following PARTITION BY and ordered by a list of expressions in the ORDER BY Clause.
Can be used with the RANK() and DENSE_RANK(),SUM,MIN,MAX,ROW_NUMBER() functions. ROWS
UNBOUNDED PRECEDING at the end allows for CUMULATIVE Calculations.
Example:
SELECT EMPLOYEEID, CUSTOMERID,
/* number of customers per employee */ SUM(1) AS NoOfCustsPerEmployee,
/* Total number of Custs per employee -note, same total in each row within Customerid */
SUM ( SUM(1) )
OVER ( PARTITION BY EMPLOYEEID ORDER BY CUSTOMERID ) AS NoOfAllCustsPerEmployee,
/* Cumulative sum of Customerid within Employeeid */
SUM ( SUM(1) )
OVER ( PARTITION BY employeeid ORDER BY customerid ROWS UNBOUNDED PRECEDING )
AS CumulativeSumOfCustsPerEmp
FROM ORDERS
GROUP BY EMPLOYEEID,CUSTOMERID
Comments
Please sign in to leave a comment.