In simple terms the OVER clause in Oracle SQL / PLSQL specifies the partition or order in which an analytical function will operate.
Syntax for the OVER clause in Oracle SQL / PLSQL is:
,aggregate_function OVER (PARTITION BY column(s))
Using OVER clause
Suppose we have a table named ‘employee’ as shown below:
If we write our query as:
SELECT employee_id ,employee_name ,department ,COUNT(*) OVER (PARTITION BY department) Total FROM employee;
We will get the following result:
Here we have used the OVER clause to get ‘Total’ column where we have retrieved ‘2’ for ‘IT’ department as there are ‘2’ records available in employee table for ‘IT’ department similarly we have ‘3’ and ‘1’ records for ‘Sales’ and ‘Support’ departments.