Partition by in Oracle

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:

If we write our query as:

1 2 3 4 5

SELECT employee_id        ,employee_name        ,department        ,COUNT(*) OVER (PARTITION BY department) Total FROM employee;

We will get the following result:

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.
 Refer for Analytic functions: http://www.orafaq.com/node/55

.