PARTITION BY Keyword in Oracle SQL

In simple terms the PARTITION BY keyword in Oracle SQL / PLSQL is used to partition or segregate the records based on groups

Syntax for the PARTITION BY keyword in Oracle SQL / PLSQL is:
SELECT columns
,aggregate_function OVER (PARTITION BY column(s))
FROM table_name;

Example 1:

Using PARTITION BY keyword

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 can see that in the ‘Total’ column 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.