Aggregate Functions | Relational Databases

In this chapter

We'll meet SQL's complete practical set of aggregate functions — COUNT, COUNT(DISTINCT), SUM, AVG, MIN, MAX, and GROUP_CONCAT — and the single most common surprise around them: how they treat NULL.

14–17 min

The Problem in Real Life

Last chapter, Sarah handed Mike exactly what he asked for: five rows, sorted by how much each customer owed. Mike looks at the list, then starts doing something Sarah didn't expect — adding the numbers up on a scrap of paper, by hand.

Sarah realizes he's right to be annoyed. SELECT gave him rows. It never once gave him an actual answer — a single number he could act on without doing arithmetic himself.

Table — The List Mike Got Last Chapter

Name	BalanceDue
Priya	140
Maria	75
Farah	50
Raj	20
Leah	15

Mike still has to add these five numbers up himself to get one real answer.

Why am I doing this by hand? Isn't this the computer's job?

Mike

Rows to Add Up vs. One Real Number

Rows aren't answers

SELECT can list every customer's balance; it can't add them up into one number by itself.

Doing arithmetic by hand doesn't scale

Adding five numbers on paper is fine. Adding eleven thousand isn't.

NULL doesn't count as zero

A column with missing values can silently change what COUNT, SUM, and AVG actually return.

One question, eight different functions

"How many," "how many different," "how much," and "on average" are all separate questions, each needing its own aggregate.

What Are Aggregate Functions, Really?

SELECT, WHERE, and ORDER BY are all about which rows come back, and in what order. None of them summarize anything — if Mike wants one number instead of a list, somebody still has to add it up by hand. SQL has a set of functions built exactly for that: aggregate functions, which take an entire column of values and collapse it down into a single result.

Here's the complete practical set, one function at a time — each one's definition sitting right below the exact query that runs it, against GreenMart's own data:

COUNT(*) counts every row in a table, no matter what's inside them — it never looks inside any column, so NULL never affects it.

COUNT(*) — How Many Rows Are There, Total?

SELECT COUNT(*) AS TotalCustomers
FROM Customers;

Here, it counts all eleven customers, Sana included.

COUNT(column) counts only the rows where that specific column isn't NULL.

COUNT(column) — How Many Actually Have a Value?

SELECT COUNT(Phone) AS CustomersWithPhone
FROM Customers;

Here, the answer is ten, not eleven — Sana's row is silently skipped, because her phone number is NULL, not zero or blank.

COUNT(DISTINCT column) counts how many different, non-NULL values actually appear, collapsing repeats down to one.

COUNT(DISTINCT column) — How Many Different Values?

SELECT COUNT(DISTINCT CustomerID) AS UniqueBuyers
FROM Sales;

Here, twelve sales happened, but only nine different customers made them — some, like Priya, show up more than once in the Sales table.

SUM(column) adds up every value in a numeric column.

SUM — Adding Up an Entire Column

SELECT SUM(BalanceDue) AS TotalOwed
FROM Customers;

Here, it's the total across every customer, done in a single query instead of by hand.

AVG(column) returns the mean of every value in a numeric column — NULLs are skipped the same way SUM and COUNT(column) skip them.

AVG — The Mean of an Entire Column

SELECT AVG(BalanceDue) AS AvgBalance
FROM Customers;

Here, it's the average across all eleven customers, though BalanceDue never actually has any NULLs to skip.

MIN(column) returns the smallest value in a column.

MIN — The Smallest Value in a Column

SELECT MIN(BalanceDue) AS SmallestBalance
FROM Customers;

Here, several customers are actually tied at 0.

MAX(column) returns the largest value in a column.

MAX — The Largest Value in a Column

SELECT MAX(BalanceDue) AS LargestBalance
FROM Customers;

Here, Priya, at 140, is still the biggest debtor after her partial payment back in the DML chapter.

GROUP_CONCAT(column) joins every value in a column into one comma-separated string — most databases have this under a different name, like STRING_AGG.

GROUP_CONCAT — Joining a Column Into One String

SELECT GROUP_CONCAT(Name, ', ') AS AllProducts
FROM Products;

Here, every product name ends up on a single line, handy for a quick summary, not for reporting on individual rows.

Compare the first three queries: COUNT(*) returned 11, COUNT(Phone) returned 10, and COUNT(DISTINCT CustomerID) on the Sales table returned 9 out of 12 rows. Three different questions, three different numbers, from what looks like almost the same function — COUNT(*) never checks a column's contents, COUNT(column) skips NULLs, and COUNT(DISTINCT column) additionally collapses repeats down to one.

Mike finally has real numbers instead of rows to add up himself — a total owed, an average balance, even every product name on one line. The one figure he originally asked for, all the way back in chapter 3, is still one JOIN away: knowing how many sales happened today isn't the same as knowing their dollar value, and that connection is still waiting in Act 3.

Key Takeaway

Aggregate functions collapse an entire column down into a single value — and every one of them except COUNT(*) quietly skips NULLs rather than treating them as zero.

Why This Matters

Almost every real business question is really an aggregate in disguise — not "show me every sale" but "how many," "how much," or "on average." The NULL-skipping behavior in this chapter isn't a side note, either: it's exactly the kind of silent, easy-to-miss difference that can quietly produce a wrong report months later if nobody ever noticed it.

GreenMart can finally answer questions with a single number instead of a list. The next chapter isn't about a new command at all — it's about the order SQL actually runs the pieces already learned, which explains a few surprises still to come.