Course contentsShow
← Data Science
Lesson 888 of 2,14523. SQL Aggregation and GroupingFree lesson

Handling NULL Values in Aggregates

Understanding how aggregate functions treat NULL values and using COALESCE to provide default values.

Handling NULL Values in Aggregates

You'll learn how aggregate functions treat NULL values and how to provide default values when needed.

When working with real-world data, you'll inevitably encounter NULL values—missing or unknown data. Understanding how SQL aggregates handle these is crucial for accurate analysis.

How Aggregates Treat NULL Values

Most aggregate functions automatically ignore NULL values. This is usually helpful but can sometimes produce unexpected results:

  • COUNT(column_name) counts only non-NULL values in that column
  • COUNT(*) counts all rows, including those with NULLs
  • SUM(), AVG(), MIN(), and MAX() all skip NULL values entirely

The Hidden Danger

If you're calculating an average and some values are NULL, the function divides by the count of non-NULL values, not the total rows. This might be what you want—or it might silently give you the wrong answer if you expected NULLs to count as zeros.

Using COALESCE for Control

The COALESCE function lets you replace NULL values with a default value of your choice. It returns the first non-NULL value from its arguments:

SELECT 
    AVG(COALESCE(sales_amount, 0)) AS avg_sales
FROM orders;

This treats NULL sales as zero, including them in your average calculation.

Practical Example

SELECT 
    COUNT(*) AS total_customers,
    COUNT(email) AS customers_with_email,
    SUM(purchase_amount) AS total_sales,
    AVG(COALESCE(purchase_amount, 0)) AS avg_including_zeros
FROM customers;

Key Takeaway: Aggregate functions ignore NULL values by default; use COALESCE to explicitly control how NULLs are treated, ensuring your calculations match your business logic.