• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / sql / PARTITION BY Keyword in Oracle SQL – PLSQL

PARTITION BY Keyword in Oracle SQL – PLSQL

November 7, 2012 by techhoneyadmin

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.


Filed Under: sql Tagged With: how to use partition_by keyword in oracle database query, how to use partition_by keyword in oracle plsql, how to use partition_by keyword in oracle sql, PARTITIONBYPLSQL, partition_by keyword in oracle plsql, partition_by keyword in oracle sql, syntax and example of partition_by keyword in oracle database query, syntax and example of partition_by keyword in oracle plsql, syntax and example of partition_by keyword in oracle sql, using partition_by keyword in oracle database query, using partition_by keyword in oracle plsql, using partition_by keyword in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in