Aggregate functions are the part of SQL that turns a table full of rows into a single meaningful number: a total, an average, a count, a maximum. Master five core functions and two supporting ideas, GROUP BY and HAVING, and you can answer most reporting questions a business will ever ask. This guide walks through each aggregate with runnable SQL and the exact result, then covers the NULL rules and grouping mechanics that quietly change your numbers if you get them wrong.
What this guide covers
What an aggregate function is
A scalar function such as UPPER or ROUND runs once per row and gives one result per row. An aggregate function does the opposite: it reads many rows and collapses them into a single value. Feed SUM a thousand order amounts and you get back one total. Feed COUNT the whole table and you get one number. This collapsing behaviour is the defining trait, and it is why aggregates behave differently from every other function in the language.
An aggregate can run over the entire result set, producing exactly one row, or it can run once per group when you add a GROUP BY clause. Both modes are covered below. Aggregates are allowed in the SELECT list and in the HAVING clause, but never in WHERE, because WHERE is evaluated before rows are grouped. If aggregates feel new, the broader SQL functions overview and the categorized SQL functions list put them in context alongside scalar functions.
The one rule to remember: every aggregate except COUNT(*) ignores NULL values. That single fact explains almost every surprising count or average you will ever debug. The NULL section below makes it concrete.
COUNT and its three forms
COUNT looks simple but has three distinct forms that return different numbers, and mixing them up is the most common aggregate mistake. The forms are COUNT(*), COUNT(column), and COUNT(DISTINCT column).
COUNT(*)counts every row, including rows that are entirely or partly NULL.COUNT(column)counts only rows where that column is not NULL.COUNT(DISTINCT column)counts the number of unique non-NULL values.
-- Sample: 5 orders, 2 of them have a NULL coupon_code
SELECT
COUNT(*) AS all_rows,
COUNT(coupon_code) AS with_coupon,
COUNT(DISTINCT coupon_code) AS unique_coupons
FROM orders;all_rows | with_coupon | unique_coupons
5 | 3 | 2All three counts came from the same table, yet returned 5, 3, and 2. The gap between all_rows and with_coupon is exactly the number of NULL coupons. If you ever see a count that looks too low, check whether you counted a nullable column instead of using COUNT(*).
COUNT(DISTINCT ...) is the natural tool for cardinality questions: how many unique customers ordered today, how many distinct products sold this week. Because the engine must track every value it has already seen, distinct counting is more expensive than a plain count on large tables, so reach for it only when uniqueness genuinely matters.
SUM, AVG, MIN, and MAX
These four cover the bulk of numeric reporting. SUM adds values, AVG averages them, and MIN and MAX find the extremes. MIN and MAX are not limited to numbers: they also work on text, where they compare alphabetically, and on dates, where they find the earliest and latest.
SELECT
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order,
MIN(amount) AS cheapest,
MAX(amount) AS largest,
MIN(created_at) AS first_order,
MAX(created_at) AS latest_order
FROM orders;total_revenue | avg_order | cheapest | largest | first_order | latest_order
48250.00 | 193.00 | 9.99 | 499.00 | 2021-03-01 | 2026-07-04Every aggregate also accepts DISTINCT, which deduplicates values before the calculation. SUM(DISTINCT x) adds each unique value once, and AVG(DISTINCT x) averages the unique set. This matters more than people expect:
-- amounts in the table: 100, 100, 200
SELECT SUM(amount) AS plain, -- 400
SUM(DISTINCT amount) AS distinct_sum -- 300
FROM payments;The plain sum treats the two 100s as separate payments and totals 400. The distinct sum collapses the duplicates and totals 300. Neither is wrong; they answer different questions. Wrapping calculations in ROUND is common when you present an average, since raw averages often carry many decimal places. For the full menu of numeric helpers you can nest inside an aggregate, the SQL cheat sheet is a fast one-page lookup.
GROUP_CONCAT and STRING_AGG
Sometimes you do not want a number: you want the values of a group joined into one readable string, like every tag on a post separated by commas. That is a string aggregate. MySQL calls it GROUP_CONCAT; PostgreSQL and SQL Server call it STRING_AGG. Both support an explicit separator and an ordering.
SELECT post_id,
GROUP_CONCAT(tag ORDER BY tag SEPARATOR ', ') AS tags
FROM post_tags
GROUP BY post_id;SELECT post_id,
STRING_AGG(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY post_id;post_id | tags
1 | index, sql, tuning
2 | joins, queryThe full syntax and options are documented in the official references: MySQL covers GROUP_CONCAT in its aggregate function reference, and Microsoft documents STRING_AGG in the T-SQL function docs.
Watch out: MySQL silently truncates GROUP_CONCAT output at group_concat_max_len, which defaults to 1024 bytes. Long lists lose values with no error and no warning. Raise the limit with SET SESSION group_concat_max_len = 1000000; before you rely on it for anything wide.
How aggregates handle NULL
This is the section that saves you from wrong reports. With the single exception of COUNT(*), every aggregate skips NULL values entirely. It does not treat them as zero, and it does not treat them as an empty string. It acts as though those rows were not there at all.
The clearest way to see it is with AVG. An average is a total divided by a count, and the count is the number of non-NULL values, not the number of rows.
-- scores in the table: 10, 20, NULL
SELECT
SUM(score) AS total, -- 30
COUNT(*) AS row_count, -- 3
COUNT(score) AS non_null_count, -- 2
AVG(score) AS avg_ignoring_null, -- 15.00
AVG(COALESCE(score, 0)) AS avg_null_as_zero -- 10.00
FROM tests;total | row_count | non_null_count | avg_ignoring_null | avg_null_as_zero
30 | 3 | 2 | 15.00 | 10.00The default average is 30 divided by 2, which is 15. If your business rule says a missing score should count as zero, you have to say so explicitly with COALESCE or IFNULL, which turns the average into 30 divided by 3, which is 10. Decide which one your report needs; the database will not decide for you. The PostgreSQL manual states the skip-NULL behaviour plainly in its aggregate function documentation, and it holds across every major engine.
One edge case is worth memorising: if you aggregate a column and every value is NULL, or the group is empty, SUM, AVG, MIN, and MAX return NULL, not zero. Only COUNT returns 0 for an empty input. Wrap the result in COALESCE(SUM(x), 0) when a report must show a numeric zero instead of a blank. For a deeper treatment of three-valued logic, see the dedicated guide on SQL functions and how NULL flows through them.
GROUP BY: one row per group
Aggregates become powerful the moment you add GROUP BY. Instead of folding the whole table into one row, the engine splits rows into buckets that share the same grouping value, then runs the aggregate once per bucket. The result has one row per group.
SELECT category,
COUNT(*) AS products,
SUM(revenue) AS total_rev,
ROUND(AVG(revenue), 2) AS avg_rev
FROM sales
GROUP BY category
ORDER BY total_rev DESC;category | products | total_rev | avg_rev
Hardware | 48 | 91200.00 | 1900.00
Software | 31 | 40300.00 | 1300.00
Media | 22 | 9900.00 | 450.00There is one strict rule that trips up beginners: every column in the SELECT list must either be inside an aggregate or listed in GROUP BY. You cannot select a bare product_name alongside SUM(revenue) grouped only by category, because there are many product names per category and the engine has no single value to show. Standard SQL and most engines reject this. MySQL historically allowed it and returned an arbitrary value, which is a data-quality trap, so keep your SELECT list disciplined.
You can group by more than one column to build a finer breakdown, for example GROUP BY category, region, which produces one row per unique category and region pair. The clause ordering of a full query is covered in depth in the guide on ORDER BY, GROUP BY, and HAVING.
HAVING: filtering groups
Once you have grouped rows, you often want to keep only certain groups: categories whose total sales beat a threshold, customers with more than five orders. You cannot do this in WHERE, because WHERE runs before grouping and knows nothing about the aggregate. The clause for filtering after aggregation is HAVING.
SELECT category,
SUM(revenue) AS total_rev
FROM sales
WHERE status = 'shipped' -- filters rows BEFORE grouping
GROUP BY category
HAVING SUM(revenue) > 10000 -- filters GROUPS after aggregation
ORDER BY total_rev DESC;category | total_rev
Hardware | 91200.00
Software | 40300.00The Media category was dropped because its total fell below 10000. Note how WHERE and HAVING cooperate: WHERE throws away individual unshipped rows first, then the aggregate runs, then HAVING throws away the whole groups that miss the cut. The full logical order is WHERE, then GROUP BY, then HAVING, then ORDER BY. Putting an aggregate in WHERE is a syntax error, and that error is the engine telling you to use HAVING instead.
| Aspect | WHERE | HAVING |
|---|---|---|
| Runs | Before grouping | After grouping |
| Filters | Individual rows | Whole groups |
| Can use aggregates? | No | Yes |
| Can use plain columns? | Yes | Only grouped columns |
| Typical use | WHERE status = 'shipped' | HAVING SUM(revenue) > 10000 |
Conditional aggregation with CASE
One of the most useful tricks in all of SQL is putting a CASE expression inside an aggregate. Because aggregates ignore NULL and SUM adds numbers, you can count or total only the rows that match a condition, all in one pass over the table. This is how you build pivot-style reports without leaving standard SQL.
SELECT
COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded,
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_revenue
FROM orders;total_orders | paid | refunded | paid_revenue
1000 | 842 | 58 | 46180.00Each SUM(CASE ...) produces a column that totals only the matching rows, so a single query returns a whole dashboard row. You can pivot on any dimension: months across the top, product categories, payment methods. A cleaner variant uses COUNT with a CASE that returns NULL for non-matches, since COUNT skips NULL: COUNT(CASE WHEN status = 'paid' THEN 1 END) counts the paid rows just as well. Both idioms are worth knowing because you will read code that uses each.
Aggregates versus window functions: conditional aggregation collapses rows into a summary. If you need a running total or a per-row rank that keeps every original row, that is a window function, not an aggregate. The two look similar but solve different problems; the SQL window functions guide draws the line clearly.
ROLLUP and GROUPING SETS
When a report needs subtotals and a grand total alongside the detail rows, you could run several queries and stitch them together, or you can ask the database for all of it at once. GROUP BY ... WITH ROLLUP adds super-aggregate rows that roll each group up to higher levels and finish with a grand total.
SELECT category, region,
SUM(revenue) AS total_rev
FROM sales
GROUP BY category, region WITH ROLLUP;category | region | total_rev
Hardware | East | 50000.00
Hardware | West | 41200.00
Hardware | NULL | 91200.00 -- subtotal for Hardware
Software | East | 40300.00
Software | NULL | 40300.00 -- subtotal for Software
NULL | NULL | 131500.00 -- grand totalThe NULLs in the grouping columns mark the super-aggregate rows: a NULL region means "all regions for this category", and NULL in both means the grand total. PostgreSQL and SQL Server write it as GROUP BY ROLLUP(category, region) and also offer GROUPING SETS, which lets you name exactly which combinations of subtotals you want rather than the fixed hierarchy ROLLUP produces. Use the GROUPING() function to tell a real NULL in your data apart from a subtotal marker. These are advanced reporting tools; reach for them once plain GROUP BY feels repetitive.
Function reference table
Here is the whole core set in one place. The "Ignores NULL" column is the behaviour to internalise, since it is the source of most aggregate surprises.
| Function | Returns | Ignores NULL? | Example |
|---|---|---|---|
| COUNT(*) | Number of rows | No, counts every row | COUNT(*) → 1000 |
| COUNT(col) | Number of non-NULL values | Yes | COUNT(coupon) → 842 |
| COUNT(DISTINCT col) | Number of unique non-NULL values | Yes | COUNT(DISTINCT city) → 17 |
| SUM | Total of numeric values | Yes | SUM(amount) → 48250.00 |
| AVG | Mean of non-NULL values | Yes | AVG(price) → 19.99 |
| MIN | Smallest value (number, text, date) | Yes | MIN(created_at) → 2021-03-01 |
| MAX | Largest value (number, text, date) | Yes | MAX(price) → 499.00 |
| GROUP_CONCAT | Group values joined into one string (MySQL) | Yes | GROUP_CONCAT(tag) → sql,index |
| STRING_AGG | Same idea (PostgreSQL, SQL Server) | Yes | STRING_AGG(tag, ',') → sql,index |
The chart below shows roughly how often each aggregate appears in everyday reporting SQL. COUNT and SUM dominate; the string aggregates are handy but niche. Learn the top of the list cold before worrying about the rest.
Quick answers
What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts every row, including rows with NULLs. COUNT(column) counts only rows where that column is not NULL. Add DISTINCT to count unique non-NULL values instead.
Why is my AVG higher than I expected?
Because AVG ignores NULLs, so it divides the total by the count of non-NULL values, not the row count. If missing values should count as zero, average COALESCE(col, 0) instead of the bare column.
Why can I not use an aggregate in WHERE?
WHERE runs before rows are grouped, so no aggregate exists yet. Filter groups with HAVING, which runs after aggregation. The clause order guide walks through why.
How do I count only rows that meet a condition?
Use conditional aggregation: SUM(CASE WHEN condition THEN 1 ELSE 0 END), or COUNT(CASE WHEN condition THEN 1 END) since COUNT skips NULL. Both count matching rows in a single pass.
Where can I practise all of this?
Work through the hands-on lessons in the beginner SQL course, keep the SQL cheat sheet open for syntax, and run every example above against your own tables as you read.