Lets take geography is a dimension. You might have 3 levels of hierarchy - city, county and state. You may wish to produce sum of revenue amounts across all three levels.
we can produce below samples
- Produce a sum of revenue amounts across all three levels.
- Aggregate at the lowest level (city) and 'rollup' those aggregations to the next level (county) and finally rollup to the top level (state).
- Provide a grand total across all three levels.
Example1:
Using the GROUP BY function, produce a total of salaries by department for department numbers less than 402.
SELECT department_number
,SUM(salary_amount)
FROM employee WHERE department_number < 402
GROUP BY 1
ORDER BY 1;
department_number
|
Group Sum(salary_amount)
|
100
|
100000
|
201
|
73450
|
301
|
116400
|
302
|
56500
|
401
|
245575
|
ROLLUP Function - One Level
Using the ROLLUP function, produce salary totals by department.
SELECT department_number
,SUM(salary_amount)
FROM employee
WHERE department_number < 402
GROUP BY ROLLUP (department_number)
ORDER BY 1;
department_number
|
Group Sum(salary_amount)
|
?
|
591925
|
100
|
100000
|
201
|
73450
|
301
|
116400
|
302
|
56500
|
401
|
245575
|
There is an additional row with a '?' department number. This row represents the rollup of all department salaries which is the top (and only) level of the hierarchy in this case. The '?' does not represent a null, rather it represents the 'total' of all department salaries.
ROLLUP Function - Two Levels
Produce a hierarchy of total salaries by department within manager.
SELECT manager_employee_number AS Mgr
,department_number AS Dept
,SUM(salary_amount) AS SumSal
FROM employee
WHERE department_number < 402
GROUP BY ROLLUP (manager_employee_number,department_number)
ORDER BY 1,2;
Mgr
|
Dept
|
SumSal
| |
?
|
?
|
591925
| (all mgrs,all depts,all emps) |
801
|
?
|
286750
| (mgr 801,all depts, 5 emps) |
801
|
100
|
100000
| (mgr 801,dept 100, 1 emp) |
801
|
201
|
34700
| (mgr 801,dept 201, 1 emp) |
801
|
301
|
57700
| (mgr 801,dept 301, 1 emp) |
801
|
302
|
56500
| (mgr 801,dept 302, 1 emp) |
801
|
401
|
37850
| (mgr 801,dept 401, 1 emp) |
1003
|
?
|
207725
| (mgr 1003,all depts, 6 emps) |
1003
|
401
|
207725
| (mgr 1003,dept 401, 6 emps) |
1019
|
?
|
58700
| (mgr 1019,all depts, 2 emps) |
1019
|
301
|
58700
| (mgr 1019,dept 301, 2 emps) |
1025
|
?
|
38750
| (mgr 1025,all depts, 1 emp) |
1025
|
201
|
38750
| (mgr 1025,dept 201, 1 emp) |
Three separate aggregations taking place in the preceding query.
Totals for each dept/manager combo (black).
This is represented as the set '(manager,department )' and is the bottom of the hierarchy.
Totals for each manager across all departments (red).
This is represented as the set '(manager )' which is the middle of the hierarchy.
A total for all depts across all managers (blue).
This is represented as the set '( )' and is the top of the hierarchy.
ROLLUP WITH NULL
As previously indicated, the row with the '?' represents the grand total of all salaries across all departments.
But what if there was a department number with a null? How would we distinguish between it and a 'total' row?
Add an employee with a null department and a null job code:
INSERT INTO employee VALUES
(1050,801,NULL,NULL,'LaCoste','Jason',780415,480816,60000.00);
SELECT department_number
,SUM(salary_amount)
FROM employee
GROUP BY ROLLUP (department_number)
ORDER BY 1;
department_number
|
Group Sum(salary_amount)
|
?
|
651925
|
?
|
60000
|
100
|
100000
|
201
|
73450
|
301
|
116400
|
302
|
56500
|
401
|
245575
|
While it may seem obvious that the row with the larger sum will represent the 'total' row,
a function exists which permits the report to be more easily read.
