unbounded preceding clause without partition by in oracle plsql

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:
SELECT columns
,aggregate_function () OVER (ORDER BY column(s) ROWS UNBOUNDED PRECEEDING)
FROM table_name
GROUP BY (column(s));

Example:

Using the UNBOUNDED PRECEDING clause without 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 (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:

Department	Employee_Name	Employee_Salary	Cumulative_Salary
IT	Emp B	20000	20000
IT	Emp C	28000	48000
Sales	Emp A	10000	58000
Sales	Emp E	32000	90000
Sales	Emp F	40000	130000
Support	Emp D	30000	160000

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

• 1^st and 2^nd 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’
• 3^rd, 4^th and 5^th records (for Emp A, Emp E amd Emp F) correspond to Sales department.
• The ‘cumulative_salary’ column in the 3^rd 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 4^th and 5^th 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 6^th 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’.