An aggregate function takes a set of individual row values and condenses them into a single summary value. That’s the core idea behind SUM, COUNT, AVG, MAX, and MIN — the five functions this tutorial pulls together into one complete reference, after covering each of them piece by piece earlier in this series. The focus here is on the specific behaviors that trip up beginners, especially NULL handling and how these functions interact with GROUP BY.
If you’ve already read the GROUP BY tutorial, much of the underlying logic here will feel familiar, since aggregate functions and GROUP BY are tightly linked concepts. Rather than re-covering that broader mental model, this tutorial zeroes in on each function’s individual quirks and edge cases.
What Makes a Function “Aggregate”
An aggregate function collapses many individual row values down into one combined summary value. That sets it apart from most other SQL expressions, which typically operate on and return a value for each individual row rather than producing a single result representing many rows at once.
You’ll see aggregate functions used in two distinct contexts: on their own, with no GROUP BY, producing one overall summary value for your entire result set, or paired with GROUP BY, producing a separate summary value for each group your data has been divided into — a pattern covered in detail in the GROUP BY tutorial.
SUM: Adding Values Together
SUM adds up every value in a specified column, across whatever set of rows it’s applied to — your entire result, or each individual group when paired with GROUP BY.
SUM ignores NULL values rather than letting them derail the calculation. When a column mixes real numbers with some NULLs, SUM totals just the non-NULL numbers, treating NULL rows as though they weren’t part of the calculation at all. The result doesn’t become NULL simply because a few individual values happened to be missing.
One edge case worth flagging: if every value being summed is NULL — or if there are no rows to sum in the first place — SUM returns NULL, not zero. That distinction matters: NULL signals “there was nothing to sum,” while zero signals “we summed real values and the total genuinely came out to zero.” Depending on your business context, this difference can change how you interpret or display the result.
COUNT: Counting Rows or Non-NULL Values
COUNT behaves in two meaningfully different ways depending on how it’s written — a point briefly touched on in the NULL tutorial, but worth spelling out fully here.
COUNT(*) counts every row in the set, regardless of whether any column within those rows contains NULL. It’s simply counting rows as they exist.
COUNT applied to a specific named column only counts rows where that column is NOT NULL, leaving out any row where the column has no recorded value. As a result, COUNT(*) and COUNT(column) can return different numbers from the exact same rows, whenever that column contains any NULLs.
COUNT DISTINCT — placing the word DISTINCT directly before the column name inside COUNT — counts only unique values, rather than every occurrence including duplicates. This is useful for something like “how many unique customers placed an order” against an orders table where a customer might show up across several separate order rows: a plain COUNT would tally every order row, while COUNT DISTINCT on the customer ID column would count each customer once, no matter how many orders they placed.
AVG: Calculating the Average
AVG calculates the arithmetic mean of a specified column’s values across whatever rows it’s applied to.
AVG also ignores NULL values, much like SUM, but with one detail worth understanding clearly: it divides the sum of non-NULL values by the count of non-NULL values — not by the total row count, NULLs included. So if a column has five rows and two are NULL, AVG averages across the remaining three, dividing their sum by three rather than by five. That can produce a noticeably different result than you’d expect if you assumed NULLs were quietly counted as zero — an assumption that’s usually the wrong way to interpret “average” for most business purposes.
If you specifically want NULLs treated as zero for averaging purposes, rather than excluded from both the sum and the count, you’ll need to convert them explicitly using COALESCE (covered in the NULL tutorial) before running AVG. That produces a meaningfully different calculation than AVG’s default behavior, so make sure it actually matches the question you’re trying to answer.
MAX and MIN: Finding Extremes
MAX returns the largest value in a specified column across whatever rows it’s applied to; MIN returns the smallest. Both ignore NULL values when determining that extreme, following the same NULL-excluding pattern as SUM, COUNT(column), and AVG.
MAX and MIN aren’t limited to numeric columns — they also work on date columns (returning the earliest or latest date) and even text columns (returning the alphabetically first or last value), though numeric and date-based columns tend to be their most common use in everyday business reporting.
Combining Multiple Aggregate Functions in One Query
Nothing limits you to a single aggregate function per query. Listing several aggregate expressions side by side in the same SELECT statement, each calculating something different from the same underlying rows, is both valid and common in real-world reporting.
For instance, a single query might calculate SUM of sale amount, COUNT(*), and AVG of sale amount together in one SELECT, FROM the same sales table, optionally combined with a shared GROUP BY clause if you want those calculations broken down per group rather than collapsed into one overall result.
The GROUP BY Connection, Restated Briefly
As the dedicated GROUP BY tutorial covers in full, any column in your SELECT statement that isn’t wrapped in an aggregate function must also appear in your GROUP BY clause. The database needs one consistent value to display per group — it can’t display several conflicting values for the same group with no clear answer to show.
Without a GROUP BY clause, aggregate functions treat your entire result set as a single implicit group, producing one overall summary row. There’s no requirement for explicit grouping syntax to be present at all times — the function simply defaults to treating everything as one group.
A Common Mistake: Using WHERE to Filter Based on an Aggregate Result
As the GROUP BY tutorial’s discussion of HAVING explains, trying to filter on an aggregate result using WHERE — say, WHERE on a SUM total — will throw an error, because that aggregate value doesn’t exist yet at the point WHERE gets evaluated in SQL’s logical execution order. HAVING exists precisely to handle this situation, filtering on values that only come into being after grouping and aggregation have already run, while WHERE’s job stays limited to filtering individual raw rows before any aggregation happens.
A Quick Reference Summary
| Function | What It Calculates | NULL Behavior |
|---|---|---|
| SUM | Total of all values | Ignores NULLs; result is NULL only if every value is NULL |
| COUNT(*) | Number of rows | Counts every row regardless of NULL content |
| COUNT(column) | Number of non-NULL values in that column | Specifically excludes NULL values from the count |
| COUNT(DISTINCT column) | Number of unique distinct values | Excludes NULLs and counts each distinct value once |
| AVG | Arithmetic mean | Divides by count of non-NULL values, not total row count |
| MAX | Largest value | Ignores NULLs when determining the maximum |
| MIN | Smallest value | Ignores NULLs when determining the minimum |
Why This Foundational Set of Functions Matters So Much
SUM, COUNT, AVG, MAX, and MIN sit underneath the vast majority of real business reporting questions: total revenue, number of customers, average order value, highest single sale, earliest signup date, and countless variations across nearly every industry you can name. Understanding their individual behaviors — particularly around NULL handling and how each one interacts with GROUP BY and HAVING — is what separates SQL that produces trustworthy results from SQL that merely looks correct while quietly containing a calculation error traced back to one of these behaviors.
This wraps up the foundational series spanning these fifteen tutorials, moving from what a basic SELECT statement does, through JOINs, GROUP BY, subqueries, and now these aggregate functions, alongside window functions and performance optimization. Each concept builds on the ones before it, which is exactly why working through them roughly in order — rather than jumping straight to advanced topics — tends to build the sturdiest, most lasting understanding of SQL as a connected whole, rather than a pile of memorized, disconnected syntax.
Which specific aggregate function calculation is not producing the result you expected? Describe your query and the columns involved, and I can help identify whether NULL handling, GROUP BY structure, or something else is the actual cause.