Aggregate with NULL handling in SQL

Introduction

Aggregates help us summarize data, but NULL values can hide or change results. Handling NULLs correctly ensures accurate summaries.

When calculating the average price of products but some prices are missing.

When counting how many people attended an event but some attendance records are unknown.

When summing sales amounts where some sales data is not recorded.

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

Syntax

SQL

SELECT AGGREGATE_FUNCTION(column_name) FROM table_name;

Common aggregate functions: COUNT, SUM, AVG, MAX, MIN.

By default, most aggregates ignore NULL values except COUNT(*).

Examples

Counts only non-NULL values in the column.

SQL

SELECT COUNT(column_name) FROM table_name;

Counts all rows, including those with NULLs.

SQL

SELECT COUNT(*) FROM table_name;

Calculates average ignoring NULL values.

SQL

SELECT AVG(column_name) FROM table_name;

Replaces NULL with 0 before summing to include all rows.

SQL

SELECT SUM(COALESCE(column_name, 0)) FROM table_name;

Sample Program

This example shows how aggregate functions treat NULLs differently. COUNT(amount) ignores NULLs, COUNT(*) counts all rows, AVG(amount) ignores NULLs, and SUM uses COALESCE to treat NULL as zero.

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_non_null,
       COUNT(*) AS count_all,
       AVG(amount) AS average_amount,
       SUM(COALESCE(amount, 0)) AS sum_with_nulls_handled
FROM sales;

Important Notes

COALESCE(column, value) replaces NULL with the given value for calculations.

COUNT(column) counts only rows where column is not NULL.

COUNT(*) counts all rows regardless of NULLs.

Summary

Aggregate functions summarize data but usually ignore NULLs.

Use COALESCE to include NULLs as a specific value in calculations.

COUNT(*) counts all rows, COUNT(column) counts only non-NULL values.