5 Essential Oracle SQL Aggregate Functions! How to Use SUM, AVG, and COUNT

Aggregate functions are essential for data analysis and report creation in Oracle Database. This article explains the usage of the five basic functions: SUM, AVG, MAX, MIN, and COUNT. We will use practical examples from the SCOTT schema to ensure you learn potential pitfalls for beginners, such as handling NULLs and combining functions with GROUP BY.

Note: This article uses examples based on the SCOTT sample 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 & Quick Reference
1. Background and Basics: How Aggregate Functions Work
2. Execution Environment and Preparation
3. Practice: Steps to Use Aggregate Functions
4. Application: Combining All Aggregate Functions
5. Troubleshooting
6. Operational, Monitoring, and Security Notes
7. FAQ: Frequently Asked Questions
Summary

Conclusion & Quick Reference

Aggregate functions (group functions) are features that summarize multiple rows and return “a single result.” First, let’s review their functions and characteristics.

Function Name	Function	Handling NULL	Target Data Types
SUM	Calculates total value	Ignored	Number
AVG	Calculates average value	Ignored	Number
MAX	Retrieves maximum value	Ignored	Number, String, Date
MIN	Retrieves minimum value	Ignored	Number, String, Date
COUNT	Counts rows/items	* includes NULL / Column ignores NULL	All

[What you will learn in this article]

The correct syntax and execution results of the 5 major aggregate functions.
The definitive difference between COUNT(*) and COUNT(column_name).
How to resolve common errors (e.g., ORA-00937).

1. Background and Basics: How Aggregate Functions Work

Normal SQL (SELECT * FROM ...) displays rows of data as they are. Aggregate functions, however, calculate data vertically (by column) and compress it into a single row.

Definition of Terms

Aggregate Function: A function that accepts multiple rows as input and returns a single value.
GROUP BY Clause: Used to group data by specific conditions (e.g., by department, by job title).
Consideration of NULL: Basically, aggregate functions ignore NULL as “non-existent” (with the exception of COUNT(*)).

2. Execution Environment and Preparation

This procedure assumes the following environment.

OS: Oracle Linux 7/8 (SQL syntax is common for Windows versions as well)
DB: Oracle Database 19c (Standard / Enterprise)
User: SCOTT schema (or a user with equivalent privileges)
Target Data: EMP Table (Employee Master)

Checking the Target Table

First, let’s verify the data we will use for our experiment.

-- Assumption: Connected via SQL*Plus or SQL Developer
SELECT EMPNO, ENAME, JOB, SAL, COMM, DEPTNO FROM EMP ORDER BY DEPTNO;

Execution Result:

SQL> SELECT EMPNO, ENAME, JOB, SAL, COMM, DEPTNO FROM EMP ORDER BY DEPTNO;

     EMPNO ENAME      JOB              SAL       COMM     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        500         30
      7844 TURNER     SALESMAN        1500          0         30
      7499 ALLEN      SALESMAN        1600        300         30
      7900 JAMES      CLERK            950                    30
      7698 BLAKE      MANAGER         2850                    30
      7654 MARTIN     SALESMAN        1250       1400         30

14 rows selected.

The data includes department codes (DEPTNO) like 10 and 20, salaries (SAL), and commissions (COMM). Note that COMM contains NULL values.

3. Practice: Steps to Use Aggregate Functions

From here, we will execute actual SQL and confirm the behavior of each function.

Step 1: SUM (Total) and AVG (Average)

Calculate the sum and average of numeric data. Since AVG often results in non-terminating decimals, it is a golden rule in practice to combine it with ROUND.

-- Get total salary and average salary by department (DEPTNO)
SELECT 
    DEPTNO, 
    SUM(SAL) AS TOTAL_SALARY, 
    ROUND(AVG(SAL), 1) AS AVG_SALARY_R 
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

Execution Result:

SQL> SELECT
  2      DEPTNO,
  3      SUM(SAL) AS TOTAL_SALARY,
  4      ROUND(AVG(SAL), 1) AS AVG_SALARY_R
  5  FROM EMP
  6  GROUP BY DEPTNO
  7  ORDER BY DEPTNO;

    DEPTNO TOTAL_SALARY AVG_SALARY_R
---------- ------------ ------------
        10         8750       2916.7
        20        10875         2175
        30         9400       1566.7

Explanation:

SUM(SAL): Simply adds up the values in the SAL column.
AVG(SAL): Calculated as (Total of SAL column) ÷ (Number of rows). Rows with NULL are not included in the denominator (row count).
GROUP BY DEPTNO: Separates the aggregation unit by department number.

Step 2: MAX (Maximum) and MIN (Minimum)

These can be used not only for numbers but also for dates (latest/oldest) and strings (dictionary order).

-- Get maximum and minimum salary by department
SELECT 
    DEPTNO, 
    MAX(SAL) AS MAX_SALARY, 
    MIN(SAL) AS MIN_SALARY
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

Execution Result:

SQL> SELECT
  2      DEPTNO,
  3      MAX(SAL) AS MAX_SALARY,
  4      MIN(SAL) AS MIN_SALARY
  5  FROM EMP
  6  GROUP BY DEPTNO
  7  ORDER BY DEPTNO;

    DEPTNO MAX_SALARY MIN_SALARY
---------- ---------- ----------
        10       5000       1300
        20       3000        800
        30       2850        950

Explanation:

Useful for understanding the range of salaries.
If you execute MAX(HIREDATE) on a date column, you can retrieve the “most recent hire date.”

Step 3: The Pitfall of the COUNT Function

The result of COUNT changes depending on “what you count.” This is the biggest trap for beginners.

Syntax	Meaning	Difference in Result
*COUNT()**	Counts the rows themselves	Returns total row count including NULLs
COUNT(column)	Counts rows containing values	Does not count rows with NULL

-- Compare "Total Members" vs "Members receiving Commission (COMM)" by department
SELECT 
    DEPTNO, 
    COUNT(*)    AS MEMBER_COUNT,  -- Total number of people belonging
    COUNT(COMM) AS COMM_HOLDER    -- Number of people where COMM is not NULL
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

Execution Result:

SQL> SELECT
  2      DEPTNO,
  3      COUNT(*)    AS MEMBER_COUNT,
  4      COUNT(COMM) AS COMM_HOLDER
  5  FROM EMP
  6  GROUP BY DEPTNO
  7  ORDER BY DEPTNO;

    DEPTNO MEMBER_COUNT COMM_HOLDER
---------- ------------ -----------
        10            3           0
        20            5           0
        30            6           4

Example Result (Dept 30):

MEMBER_COUNT: 6 people (Everyone in the department)
COMM_HOLDER: 4 people (The 2 people with NULL in COMM are not counted)

4. Application: Combining All Aggregate Functions

Here is an SQL query to create a “Departmental Statistics Report,” which is often used in practice. Copy and paste it to check the behavior.

-- SQL for creating Departmental Statistics Report
SELECT 
    DEPTNO,
    COUNT(*)           AS "Employee Count",
    SUM(SAL)           AS "Total Salary",
    ROUND(AVG(SAL), 0) AS "Average Salary",
    MAX(SAL)           AS "Max Salary",
    MIN(SAL)           AS "Min Salary"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

Execution Result:

SQL> SELECT
  2      DEPTNO,
  3      COUNT(*)           AS "Employee Count",
  4      SUM(SAL)           AS "Total Salary",
  5      ROUND(AVG(SAL), 0) AS "Average Salary",
  6      MAX(SAL)           AS "Max Salary",
  7      MIN(SAL)           AS "Min Salary"
  8  FROM EMP
  9  GROUP BY DEPTNO
 10  ORDER BY DEPTNO;

    DEPTNO Employee Count Total Salary Average Salary Max Salary Min Salary
---------- -------------- ------------ -------------- ---------- ----------
        10              3         8750           2917       5000       1300
        20              5        10875           2175       3000        800
        30              6         9400           1567       2850        950

5. Troubleshooting

Common errors encountered when using aggregate functions and their solutions.

Error Code	Error Message	Cause and Solution
ORA-00937	not a single-group group function	A mix of aggregate functions and “non-aggregated columns (raw data)” exists in the SELECT clause. Solution: Add all non-aggregated columns to `GROUP BY`.
ORA-00979	not a GROUP BY expression	You are trying to `SELECT` a column that is not specified in `GROUP BY`. Solution: Columns in the `SELECT` list must be included in `GROUP BY` or passed through an aggregate function.
ORA-01476	divisor is equal to zero	Occurs when the denominator becomes 0 in calculations like averages (e.g., manual calculation). Solution: Avoid division by zero using `DECODE` or `NULLIF`.

6. Operational, Monitoring, and Security Notes

Impact and Risks

Performance: Aggregating large numbers of rows (tens of millions) may cause a full table scan, risking high CPU/IO usage on the DB server.
Cancellation: If execution takes too long, interrupt the query using Ctrl + C in SQL*Plus or the “Cancel” button in your tool. Since there is no data modification (UPDATE/DELETE) involved, there is no risk of data corruption.

Best Practices

Filter data with the WHERE clause as much as possible before aggregation.
Consider creating indexes on columns used in GROUP BY (to reduce sorting load).

7. FAQ: Frequently Asked Questions

Q1. I want to filter the aggregated results by conditions like “Average is 2000 or more.” A. Use the HAVING clause, not the WHERE clause. WHERE filters before aggregation, while HAVING filters after aggregation.

Example: SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) >= 2000;

Q2. How do I treat NULL as 0 when calculating the average? A. Use the NVL function to convert NULL to 0 before aggregating.

Example: AVG(NVL(COMM, 0)) This ensures that rows with NULL are included in the denominator (row count), allowing for the calculation of a correct “overall average.”

Q3. Can I calculate the Median in Oracle SQL? A. Yes, the MEDIAN function is available (from 10g onwards). Since average values are easily affected by outliers (such as extremely high salaries), the median may be more suitable for understanding the reality.

Summary

Memorize the basic 5: SUM (Total), AVG (Average), MAX (Maximum), MIN (Minimum), COUNT (Number).
The NULL Trap: COUNT(*) includes NULLs, but COUNT(column) and SUM/AVG ignore NULLs.
Grouping: If displaying non-aggregated columns, strictly include them in GROUP BY (ORA-00937 countermeasure).
Performance: Exclude unnecessary rows with WHERE first, then aggregate.

Aggregate functions are both the foundation and the ultimate secret of SQL. Start by testing the difference in COUNT behavior in your own environment!

This article explains using Oracle Database 19c (screens and default values may differ in other versions).

[reference]
Oracle Database SQL Language Reference, 19c