how to use over clause in oracle database query

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.