• 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 / clause / UNBOUNDED PRECEDING Clause with PARTITION BY in Oracle SQL – PLSQL

UNBOUNDED PRECEDING Clause with PARTITION BY in Oracle SQL – PLSQL

November 19, 2012 by techhoneyadmin

The UNBOUNDED PRECEDING is a windowing clause which defines the aggregation window i.e. the extent of rows to be used in aggregation. It tells oracle, the extent from where the rows are to be aggregated in the subgroup.

Syntax for the UNBOUNDED PRECEDING clause with PARTITION BY in Oracle SQL / PLSQL is:
SELECT columns
,aggregate_function () OVER (PARTITION BY column(s) ORDER BY column(s) ROWS UNBOUNDED PRECEDING)
FROM table_name
GROUP BY (column(s));

Example:
Using the UNBOUNDED PRECEDING clause with partition by
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 department
       ,employee_name
       ,SUM(salary) EMPLOYEE_SALARY
       ,SUM(SUM(Salary)) OVER (PARTITION BY department ORDER BY department
                         ROWS UNBOUNDED PRECEDING) AS TOTAL_SALARY
FROM employee
GROUP BY department, salary, employee_name
ORDER BY department, employee_name; 

We will get the following result:

Department Employee_Name Employee_Salary Dept_Salary
IT Emp B 20000 20000
IT Emp C 28000 48000
Sales Emp A 10000 10000
Sales Emp E 32000 42000
Sales Emp F 40000 82000
Support Emp D 30000 30000

Let’s observe the records (especially the ‘Dept_Salary’ column) fetched to understand the UNBOUNDED PRECEDING clause.

  • 1st and 2nd record (for Emp B and Emp C) pertain to ‘IT’ department, the Dept_salary column for the second records gives us the total of the salary being given to the ‘Emp B’ and ‘Emp C’ in ‘IT’ Department
  • 3rd, 4th and 5th records (for Emp A, Emp E amd Emp F) correspond to Sales department.
  • The Dept_salary column of the 4th and 5th record tells us the total of salary being given to  ‘Emp A’ and ‘Emp E’ and ‘Emp A’, ‘Emp E’ and ‘Emp F’ respectively in ‘Sales’ department.
  • The 6th record corresponds to ‘Emp D’ in ‘Support’ department, the Dept_salary column for this record gives the total of salary being given to ‘Emp D’in ‘Support’ department.

Filed Under: clause Tagged With: how to use unbounded preceding clause with partition by in oracle database query, how to use unbounded preceding clause with partition by in oracle plsql, how to use unbounded preceding clause with partition by in oracle sql, syntax and example of unbounded preceding clause with partition by in oracle database query, syntax and example of unbounded preceding clause with partition by in oracle plsql, syntax and example of unbounded preceding clause with partition by in oracle sql, unbounded preceding clause with partition by in oracle plsql, unbounded preceding clause with partition by in oracle sql, UNBOUNDEDPLSQL, using unbounded preceding clause with partition by in oracle database query, using unbounded preceding clause with partition by in oracle plsql, using unbounded preceding clause with partition by in oracle sql

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