Calculating Totals and Subtotals in Oracle Sql
Calculating Totals and Subtotals in Oracle SQL
You need to calculate totals and subtotals in a variety of environments, using the lowest common
denominator of SQL. For instance, you need to count the number of people in each department, as well
as a grand total, in a way that can run across a variety of editions of Oracle without change.
In situations where you feel you can’t use analytic functions like ROLLUP and CUBE, or are restricted by
licensing or other factors, you can use traditional aggregation and grouping techniques in separate SQL
statements, and combine the results with a UNION to fold all the logic into a single statement. This SELECT
combines counts of employees by department in one query, with the count of all employees in
select nvl(to_char(department_id),’-‘) as “DEPT.”, count(*) as “EMP_COUNT”
group by department_id
select ‘All Depts.’, count(*)
The recipe results appear as follows, with abridged output to save space.
All Depts. 107
13 rows selected.