OVER() / OVER (PARTITION BY )

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
Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.