NULL behavior in aggregate functions in SQL

Introduction

NULL values represent missing or unknown data. Understanding how aggregate functions handle NULLs helps you get correct results when summarizing data.

When calculating the average salary but some employees have no salary recorded.

When counting the number of orders but some orders have missing customer IDs.

When summing sales amounts where some sales records have NULL values.

When finding the maximum score but some scores are not entered.

When grouping data and you want to know how NULLs affect totals or counts.

Syntax

SQL

AGGREGATE_FUNCTION(column_name)

-- Examples of aggregate functions:
-- COUNT(column_name), SUM(column_name), AVG(column_name), MAX(column_name), MIN(column_name)

Aggregate functions ignore NULL values except COUNT(*), which counts all rows.

COUNT(column_name) counts only non-NULL values in that column.

Examples

Counts only rows where column_name is not NULL.

SQL

SELECT COUNT(column_name) FROM table;

Counts all rows, including those with NULLs.

SQL

SELECT COUNT(*) FROM table;

Calculates average ignoring NULL values in column_name.

SQL

SELECT AVG(column_name) FROM table;

Sums values ignoring NULLs in column_name.

SQL

SELECT SUM(column_name) FROM table;

Sample Program

This example shows how aggregate functions treat NULLs in the amount column.

SQL

CREATE TABLE sales (
  id INT,
  amount INT
);

INSERT INTO sales (id, amount) VALUES
(1, 100),
(2, NULL),
(3, 200),
(4, NULL);

SELECT COUNT(amount) AS count_amount,
       COUNT(*) AS count_all,
       SUM(amount) AS sum_amount,
       AVG(amount) AS avg_amount
FROM sales;

Important Notes

NULL values are skipped in SUM, AVG, MAX, MIN, and COUNT(column_name).

COUNT(*) counts all rows regardless of NULLs.

If all values are NULL, SUM and AVG return NULL.

Summary

Aggregate functions ignore NULLs except COUNT(*) which counts all rows.

COUNT(column) counts only non-NULL values.

SUM and AVG skip NULLs but return NULL if no non-NULL values exist.