What an aggregate function actually does
Most SQL functions operate row by row — UPPER(name) transforms one value into another value, one row at a time. An aggregate function is fundamentally different: it takes a whole set of rows as input and produces a single output value, summarizing them. COUNT(*) on a 10,000-row table doesn't return 10,000 rows of counts — it returns one row with the number 10,000.
The five core aggregate functions
| Function | Returns | Example |
|---|---|---|
COUNT() | Number of rows (or non-NULL values) | COUNT(*), COUNT(email) |
SUM() | Total of a numeric column | SUM(total) |
AVG() | Arithmetic mean of a numeric column | AVG(salary) |
MIN() | Smallest value (numbers, dates, or text) | MIN(order_date) |
MAX() | Largest value (numbers, dates, or text) | MAX(salary) |
MIN() and MAX() aren't limited to numbers — they work on dates (earliest/latest) and even text (alphabetically first/last), which is easy to forget since they're usually introduced alongside purely numeric examples.
How aggregates handle NULL
This is the single most important thing to know about aggregate functions, and the one most likely to produce a surprising number in a report. SUM(), AVG(), MIN(), MAX(), and COUNT(column) all skip NULL values in the column they're aggregating — they behave as if those rows simply weren't there for the purpose of that calculation. COUNT(*) is the odd one out: it counts rows, not values, so it counts every row regardless of NULLs.
-- orders table: 5 rows, 2 have a NULL discount
SELECT
COUNT(*) AS total_rows,
COUNT(discount) AS rows_with_discount,
AVG(discount) AS avg_discount
FROM orders;
-- total_rows: 5 | rows_with_discount: 3 | avg_discount: averaged over only those 3AVG(discount) divides by 3, the count of non-NULL rows, not 5 — treating a missing discount as "no data" rather than as zero. If the intent really is to treat a missing discount as 0, wrap the column in COALESCE(discount, 0) first. See the underlying three-valued logic in NULL Handling in SQL, and the specific COUNT(*) vs COUNT(column) distinction in COUNT(*) vs COUNT(column).
SUM(), AVG(), MIN(), and MAX() all return NULL, not 0 — there's nothing to sum or average, so the honest answer is "unknown," not "zero."
Aggregates without GROUP BY
An aggregate function doesn't require GROUP BY at all. Without one, the entire result set is treated as a single implicit group, and the query returns exactly one summary row:
SELECT COUNT(*) AS total_orders, SUM(total) AS total_revenue
FROM orders;
-- always exactly one row, summarizing the whole tableThis is also why GROUP BY itself doesn't strictly require an aggregate — the two ideas are related but independent, covered fully in SQL GROUP BY Without Aggregate Function.
Aggregates with GROUP BY
Add GROUP BY, and the same aggregate function runs once per group instead of once for the whole table:
SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
GROUP BY customer_id;
-- one row per distinct customer_idEvery column in SELECT that isn't an aggregate must appear in GROUP BY — this rule, and what happens when it's violated, is covered in SQL GROUP BY Interview Questions. For pivoting rows into columns using a conditional aggregate, see SQL CASE Statement Interview Questions; for subtotal and grand-total rows layered on top of a grouped aggregate, see Advanced GROUP BY Techniques You Should Know.
Filtering aggregate results with HAVING
WHERE can't filter on an aggregate value, because WHERE runs before any aggregation happens — the aggregate simply doesn't exist yet at that point in execution. Filtering on an aggregate result requires HAVING, which runs after the aggregation completes:
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;The full reasoning behind why this ordering is fixed, not a stylistic choice, is in Why HAVING Is Used After GROUP BY.
Aggregate functions vs window functions
A plain aggregate collapses rows into fewer output rows — one per group, or one overall. A window function uses the same underlying functions (SUM(), AVG(), COUNT()) but keeps every original row while attaching a per-row computed value alongside it, using OVER(). The choice between the two comes down to whether you need a summary table or a per-row comparison to a group. Full comparison in Window Functions Explained Simply and SQL Window Function Interview Questions.
Key takeaways
- An aggregate function collapses many rows into a single summary value.
- Every aggregate except COUNT(*) silently ignores NULL values in the column it's aggregating.
- SUM, AVG, MIN, and MAX return NULL, not 0, when there's no non-NULL data to work with.
- An aggregate works fine with no GROUP BY at all — the whole table becomes one implicit group.
- HAVING, not WHERE, is required to filter on an aggregate's result.
Frequently asked questions
What is an aggregate function in SQL?
An aggregate function takes many input rows and collapses them into a single summary value, such as a count, sum, average, minimum, or maximum. The five most common are COUNT, SUM, AVG, MIN, and MAX.
Do aggregate functions ignore NULL values?
COUNT(column), SUM, AVG, MIN, and MAX all ignore NULL values in the column they're aggregating and compute their result from the remaining non-NULL rows. COUNT(*) is the one exception, since it counts rows themselves rather than values in a specific column, so it includes rows with NULL.
Can you use an aggregate function without GROUP BY?
Yes. Without GROUP BY, an aggregate function treats the entire result set as a single group and returns one row summarizing the whole table, rather than one row per group.
What does AVG return if every value in the column is NULL?
AVG, like the other aggregate functions except COUNT, returns NULL when there are no non-NULL values to aggregate, rather than zero or an error, since there's genuinely nothing to average.
Why can't you filter an aggregate result with WHERE?
WHERE filters individual rows before any aggregation happens, so the aggregate value doesn't exist yet at that point. Filtering on an aggregate result, such as a total order count, requires HAVING, which runs after the aggregation is complete.