“I was able to calculate the total salary for each department. But how do I get the average of those department totals?” When performing aggregation processing in Oracle Database, there are times when you want to use AVG or MAX on the results already grouped by GROUP BY.
This article explains techniques for further aggregating aggregation results (using subqueries and nested aggregate functions) and using ROLLUP to automatically calculate subtotals and grand totals, using the EMP table from the SCOTT schema.
You can import and use the SCOTT sample schema by executing the following:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger
Conclusion & Methods List
There are two main ways to further aggregate aggregation results:
- Use a Subquery: Treat the aggregation result as a temporary table in the
FROMclause (Standard method). - Nest Aggregate Functions: Layer functions like
AVG(SUM(SAL))(Oracle proprietary feature).
Also, ROLLUP is optimal for outputting a total line at once.
[What you will learn in this article]
- SQL to find the “Average” or “Maximum” of totals by department.
- How to output a “Grand Total” line in a single query using
ROLLUP. - Handling NULLs using
NVL.
- 1. Prerequisite Data: Checking the EMP Table
- 2. Step 1: Basic GROUP BY (Aggregation by Department)
- 3. Step 2: Further Aggregating Aggregation Results
- 4. Step 3: Automatically Adding “Grand Total” with ROLLUP
- 5. Application: Cleaning Up the Appearance (Handling NULL)
- 6. Troubleshooting & Notes
- 7. FAQ: Frequently Asked Questions
- Summary
1. Prerequisite Data: Checking the EMP Table
This time, we use the Oracle standard scott.emp table. First, let’s check the contents of the data and the target to be aggregated.
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP ORDER BY DEPTNO;
Result:
SQL> SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP ORDER BY DEPTNO;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
7782 CLARK MANAGER 2450 10
7839 KING PRESIDENT 5000 10
7934 MILLER CLERK 1300 10
7566 JONES MANAGER 2975 20
7902 FORD ANALYST 3000 20
7876 ADAMS CLERK 1100 20
7369 SMITH CLERK 800 20
7788 SCOTT ANALYST 3000 20
7521 WARD SALESMAN 1250 30
7844 TURNER SALESMAN 1500 30
7499 ALLEN SALESMAN 1600 30
7900 JAMES CLERK 950 30
7698 BLAKE MANAGER 2850 30
7654 MARTIN SALESMAN 1250 30
14 rows selected.
Data Points:
- DEPTNO (Department Number): 3 departments exist: 10, 20, and 30.
- SAL (Salary): Salary of each employee.
Goal: First, sum up SAL for each department, and then analyze those figures further.
2. Step 1: Basic GROUP BY (Aggregation by Department)
First, calculate the basic “Total Salary by Department.”
SELECT
DEPTNO,
SUM(SAL) AS DEPT_TOTAL
FROM EMP
GROUP BY DEPTNO;
Execution Result:
SQL> SELECT
2 DEPTNO,
3 SUM(SAL) AS DEPT_TOTAL
4 FROM EMP
5 GROUP BY DEPTNO;
DEPTNO DEPT_TOTAL
---------- ----------
30 9400
10 8750
20 10875
These three numbers “9400, 10875, 8750” become the input data for the next step.
3. Step 2: Further Aggregating Aggregation Results
This is the main topic. We will calculate the average value of the “Total Salary by Department (3 numbers).”
If the result is (9400 + 10875 + 8750) ÷ 3 = 9675, it is correct.
Method A: Using a Subquery (Inline View)
This is the most versatile method and works with other databases (MySQL, PostgreSQL, etc.). Place the SQL from Step 1 inside the FROM clause.
SELECT
AVG(DEPT_TOTAL) AS AVG_OF_TOTALS
FROM
(SELECT DEPTNO, SUM(SAL) AS DEPT_TOTAL
FROM EMP
GROUP BY DEPTNO);
Explanation:
The inner SELECT creates 3 rows of aggregation results, and the outer SELECT averages those 3 rows.
Method B: Nesting Aggregate Functions (Oracle Feature)
In Oracle Database, you can nest aggregate functions. It can be written with very short code.
SELECT
AVG(SUM(SAL)) AS AVG_OF_TOTALS
FROM EMP
GROUP BY DEPTNO;
Execution Result (Common to A/B):
SQL> SELECT
2 AVG(DEPT_TOTAL) AS AVG_OF_TOTALS
3 FROM
4 (SELECT DEPTNO, SUM(SAL) AS DEPT_TOTAL
5 FROM EMP
6 GROUP BY DEPTNO);
AVG_OF_TOTALS
-------------
9675
SQL> SELECT
2 AVG(SUM(SAL)) AS AVG_OF_TOTALS
3 FROM EMP
4 GROUP BY DEPTNO;
AVG_OF_TOTALS
-------------
9675
Note:
When nesting aggregate functions (e.g., AVG(SUM(SAL))), you cannot display grouping columns such as DEPTNO in the SELECT clause (because displaying individual department numbers while calculating the overall average is logically contradictory).
4. Step 3: Automatically Adding “Grand Total” with ROLLUP
“I want to output the total by department, and also list the grand total for the whole company at the end.”
To achieve this, you don’t need to connect two SQL queries with UNION ALL. Using the ROLLUP option solves it in one go.
SELECT
DEPTNO,
SUM(SAL) AS TOTAL_SAL
FROM EMP
GROUP BY ROLLUP(DEPTNO);
Execution Result:
SQL> SELECT
2 DEPTNO,
3 SUM(SAL) AS TOTAL_SAL
4 FROM EMP
5 GROUP BY ROLLUP(DEPTNO);
DEPTNO TOTAL_SAL
---------- ----------
10 8750
20 10875
30 9400
29025
Explanation:
Just by writing GROUP BY ROLLUP(DEPTNO), Oracle automatically adds an “aggregated row without grouping (Grand Total)” at the end. The row where DEPTNO is NULL represents the grand total.
5. Application: Cleaning Up the Appearance (Handling NULL)
If you are bothered by DEPTNO becoming (null) in the ROLLUP result, using the NVL or DECODE function to replace it with a string like “Total” improves the quality as a report.
SELECT
DECODE(GROUPING(DEPTNO), 1, 'Total', DEPTNO) AS DEPT_LABEL,
SUM(SAL) AS TOTAL_SAL
FROM EMP
GROUP BY ROLLUP(DEPTNO);
Execution Result:
SQL> SELECT
2 DECODE(GROUPING(DEPTNO), 1, 'Total', DEPTNO) AS DEPT_LABEL,
3 SUM(SAL) AS TOTAL_SAL
4 FROM EMP
5 GROUP BY ROLLUP(DEPTNO);
DEPT_LABEL TOTAL_SAL
---------------------------------------- ----------
10 8750
20 10875
30 9400
Total 29025
Explanation:
GROUPING(DEPTNO): A function that returns1if it is an aggregated row (row created by ROLLUP) and0if it is normal data.- Using this, we display “‘Total’ if it is an aggregated row.”
- Note: Since
DEPTNOis a numeric type, you might needTO_CHARconversion to mix in the string ‘Total’, but here it is introduced as a concept.
6. Troubleshooting & Notes
| Item | Note | Solution |
| Aggregating NULL Data | SUM and AVG ignore NULL. | If you want to treat NULL as 0, convert it like SUM(NVL(COMM, 0)). |
| Displaying Columns when Nested | SELECT DEPTNO, AVG(SUM(SAL))... causes an error (ORA-00937). | When nesting aggregation, remove the grouping key (DEPTNO) from the SELECT clause. |
| Data Type | Do not SUM non-numeric columns (like names). | Check the column definition of the aggregation target. |
7. FAQ: Frequently Asked Questions
Q1. What is the difference between GROUP BY ROLLUP and CUBE?
A. ROLLUP performs hierarchical aggregation (e.g., Year > Month > Day > Grand Total), while CUBE outputs every combination of specified columns (cross-tabulation). If you just want a simple grand total, ROLLUP is sufficient.
Q2. What happens to the ROLLUP grand total if filtered by a WHERE clause?
A. The grand total is calculated only using the data remaining after filtering by the WHERE clause. Excluded data is not included in the grand total.
Q3. Can aggregate functions be nested 3 levels deep?
A. No, even in Oracle, nesting of aggregate functions is limited to 2 levels (e.g., MAX(AVG(SUM(SAL))) is not allowed). If you need more, please use subqueries.
Summary
- Re-aggregation: If you want to further aggregate the results of
GROUP BY, use a Subquery or Nested Aggregate FunctionsAVG(SUM(col)). - Grand Total: Using
ROLLUPinstead ofUNIONis smart. - NULL: NULL display in aggregated rows can be controlled with functions like
GROUPING.
This is a technique that significantly reduces the man-hours for creating summary reports. Please utilize it in your actual work.
This article explains using Oracle Database 19c (screens and default values may differ in other versions).
[reference]
Oracle Database SQL Language Reference, 19c
コメント