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 without PARTITION BY in Oracle SQL / PLSQL is:
,aggregate_function () OVER (ORDER BY column(s) ROWS UNBOUNDED PRECEEDING)
GROUP BY (column(s));
Using the UNBOUNDED PRECEDING clause without PARTITION BY
Suppose we have a table named ‘employee’ as shown below:
If we write our query as:
SELECT department ,employee_name ,SUM(salary) EMPLOYEE_SALARY ,SUM(SUM(Salary)) OVER (ORDER BY department ROWS UNBOUNDED PRECEDING) AS CUMULATIVE_SALARY FROM employee GROUP BY department, salary, employee_name ORDER BY department, employee_name;
We will get the following result:
Let’s observe the records (especially the ‘Cumulative_Salary’ column) fetched to understand the UNBOUNDED PRECEDING clause.
- 1st and 2nd record (for Emp B and Emp C) pertain to ‘IT’ department, the cumulative_salary column for the second records gives us the total of the salary being given to the ‘Emp B’ and ‘Emp C’
- 3rd, 4th and 5th records (for Emp A, Emp E amd Emp F) correspond to Sales department.
- The ‘cumulative_salary’ column in the 3rd record tells us the total of the salary being given to ‘Emp B’, ‘Emp C’ and ‘Emp A’. Similarly the cumulative_salary culomn of the 4th and 5th record tells us the total of salary being given to ‘Emp B’, ‘Emp C’, ‘Emp A’ and ‘Emp E’ and ‘Emp B’, ‘Emp C’, ‘Emp A’, ‘Emp E’ and ‘Emp F’ respectively.
- The 6th record corresponds to ‘Emp D’ in ‘Support’ department, the cumulative_salary column for this record gives the total of salary being given to ‘Emp A’, ‘Emp B’, ‘Emp C’, ‘Emp D’, ‘Emp E, and ‘Emp F’.