OVER Clause in Oracle SQL – PLSQL

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:SELECT columns
,aggregate_function OVER (PARTITION BY column(s))
FROM table_name;

Example 1:

Using OVER clause

Suppose we have a table named ‘employee’ as shown below:

Employee_Id

Employee_Name

Salary

Department

Commission

101

Emp A

10000

Sales

10

102

Emp B

20000

IT

20

103

Emp C

28000

IT

20

104

Emp D

30000

Support

105

Emp E

32000

Sales

10

106

Emp F

40000

Sales

10

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:

Employee_Id

Employee_Name

Department

Total

103

Emp C

IT

2

102

Emp B

IT

2

106

Emp F

Sales

3

105

Emp E

Sales

3

101

Emp A

Sales

3

104

Emp D

Support

1

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.