How NULL Affects Aggregate Functions: SUM(), COUNT(), AVG(), MIN(), MAX()

Remember, aggregate functions are those that work with multiple rows of data at once and return a single result. In PostgreSQL, you'll often use these aggregate functions:

• SUM() — sums up data.
• AVG() — finds the average value.
• MIN() — finds the minimum value.
• MAX() — finds the maximum value.
• COUNT() — counts rows.

At first glance, it's simple: you pass a column or expression to the function and get a result. But what happens if the column has a NULL?

How NULL Behaves in Aggregates: Quick Overview

This is where things get interesting:

• SUM() and AVG() ignore NULL. If at least one record has a NULL value, it just doesn't get included in the calculation. Seems fair, right? How can the sum go up if someone "didn't show up to the party"? Or how do you calculate an average if you're missing a value?
• MIN() and MAX() also skip NULL. They find the minimum or maximum only among the data that's not NULL. So, if you're looking for the youngest employee and someone forgot to fill in their birth date, NULL won't win the contest.
• COUNT(*) counts all rows, even those with NULL. But COUNT(column) only counts rows where the specified column has a value, so NULL gets ignored.

Let's break this down with some examples.

Examples of Using Aggregate Functions with NULL

Here's a students_scores table with students' test scores:

Now let's run a few queries and see what happens:

1. Total score: SUM()

SELECT SUM(score) AS total_score
FROM students_scores;

Result:

As you can see, the missing NULL values just didn't get summed up. For Alice (85), Charlie (92), and Elena (74), the total is 251. Bob and Dana were left out.

2. Average score: AVG()

SELECT AVG(score) AS average_score
FROM students_scores;

Result:

Again, NULL was ignored, and the average was calculated only for those who had scores: (85 + 92 + 74) / 3 = 83.67.

3. Minimum and maximum score: MIN() and MAX()

SELECT
    MIN(score) AS min_score, 
    MAX(score) AS max_score 
FROM students_scores;

Result:

Here too, it's simple: NULL values were ignored again, so the minimum is 74 and the maximum is 92.

4. Counting rows: COUNT(*) vs COUNT(column)

SELECT
    COUNT(*) AS total_rows, 
    COUNT(score) AS non_null_scores 
FROM students_scores;

Result:

• COUNT(*) counted all rows, including those where score is NULL.
• COUNT(score) only counted rows where the score column has a value.

Practical Cases

Let's look at a few practical examples.

Example 1: Counting Employees With and Without Salary

Suppose we have an employees table with salaries.

We want to know how many employees have a salary listed and how many don't.

SELECT
    COUNT(*) AS total_employees,
    COUNT(salary) AS employees_with_salary,
    COUNT(*) - COUNT(salary) AS employees_without_salary
FROM employees;

Here's what's happening:

• COUNT(*) returns the total number of employees.
• COUNT(salary) counts how many employees have a salary listed.
• To get the number of employees without a salary, we just subtract one value from the other.

Result

Example 2: Calculating Average Product Price with Missing Data

You're the owner of a magic shop, and the products table has a price column, but some products don't have a price yet.

You need to find the average price only for products where it's set.

SELECT AVG(price) AS average_price
FROM products;

Result:

If you want to set a default price for products without a price (like, make it 0), you can use the COALESCE() function from the next lecture.

Example 3: Finding the Youngest and Oldest Student

The students table stores students' ages, but for some of them the age is unknown (NULL).

We want to find the youngest and oldest student.

SELECT
    MIN(age) AS youngest_student,
    MAX(age) AS eldest_student
FROM students;

Result:

This query will return the minimum and maximum age only for students whose age is set. NULL gets skipped again.

Things to Watch Out For

When you're working with NULL in aggregates, keep these things in mind:

• In SUM() and AVG(), NULL is ignored. You can use this to avoid adding "empty" values to your calculations.
• If you need to count rows with NULL in a column, use COUNT(*).
• When using MIN() or MAX(), NULL doesn't affect the result. But if the whole column is just NULL, the result will be NULL too.

Tips for Working with NULL

1. Think about your use case. It's important to know if you need to include NULL in your query. Sometimes, like with AVG(), ignoring them is exactly what you want. Other times, like when counting total rows, you want to include rows with NULL too.
2. Use COALESCE() if needed. If you want to replace NULL with a default value in your calculations, the COALESCE() function is your friend (but that's for the next lecture).
3. Don't mix up COUNT(*) and COUNT(column). This is a classic rookie mistake. The first counts all rows, the second only counts rows with non-null values.

Now you know how the sneaky, silent NULL can mess with your aggregates. This knowledge will help you avoid nasty surprises and use NULL to your advantage. In the next lecture, we'll check out the powerful COALESCE() tool to handle NULL even better.