We all know aggregate functions in Oracle. Let me provide a simple example to recap what the aggregate function is. Let us firse this SQL on a 107 record table:
select department_id, listagg( first_name,'-') within group (order by hire_date) from employees group by department_id;
The result is a 12 record outcome, one record for each department:
One could be interested to combine this aggregate with the original data. One then calls for a so-called analytical function. The corresponding SQL is:
select department_id, first_name, listagg( first_name,'-') within group (order by hire_date) over (partition by department_id) from employees;
The result is a 107 record outcome whereby aggregates are mixed with the original records:
A similar code can be used to interlace grouping results (as a sum) with the original records. The code is:
select department_id, salary, sum( salary) over (partition by department_id) from employees;
and the outcome:
