SQL Aggregations and GROUP BY Explained
SkillVeris Team
Data Science Team
SQL aggregation functions summarize many rows into a single value, and GROUP BY splits rows into groups so each group gets its own summary.
In this guide, you'll learn:
- The core aggregates are COUNT, SUM, AVG, MIN, and MAX, each ignoring NULL values except COUNT(*).
- GROUP BY collapses rows sharing the same column values into one row per group.
- WHERE filters rows before grouping; HAVING filters groups after aggregation.
- Every non-aggregated column in the SELECT list must appear in GROUP BY.
1What Are SQL Aggregations and GROUP BY?
SQL aggregation functions take many rows and reduce them to a single summary value, and GROUP BY divides rows into groups so each group produces its own summary. Together they answer questions like total sales per region, average order value per customer, or how many orders each product received.
Without GROUP BY, an aggregate like SUM collapses the entire table into one number. Add GROUP BY, and you get one number per group instead. This pairing is the foundation of almost every report and dashboard built on a database.
2The Core Aggregate Functions
Five aggregate functions cover the vast majority of analytical needs. Each takes a column and returns one value that summarizes all the rows in scope. Crucially, all of them except COUNT(*) skip NULL values, which is a frequent source of surprise.
COUNT reports how many rows or non-NULL values exist, SUM adds numeric values, AVG computes the mean, and MIN and MAX return the smallest and largest. You combine them freely in a single SELECT to build a compact summary.
- COUNT(*) # number of rows, including those with NULLs
- COUNT(email) # number of rows where email is not NULL
- SUM(total) # sum of the total column, ignoring NULLs
- AVG(price) # mean price of non-NULL rows
- MIN(created_at), MAX(created_at) # earliest and latest timestamps
3How GROUP BY Works
GROUP BY collapses all rows that share the same value in the grouped columns into a single output row. The database sorts or hashes rows into buckets by the grouping key, then applies each aggregate function within each bucket. The result has one row per distinct group.
For example, grouping an orders table by customer_id and selecting SUM(total) returns each customer's total spend. You can group by multiple columns to create finer buckets, such as region and month together, which produces one row per region-month combination.
- SELECT customer_id, SUM(total) AS spend
- FROM orders
- GROUP BY customer_id;
- # One row per customer, with their total spend.
4WHERE vs HAVING
WHERE and HAVING both filter, but at different stages. WHERE filters individual rows before grouping happens, so it cannot reference aggregate results. HAVING filters entire groups after aggregation, so it can test aggregate values like SUM or COUNT.
A common pattern combines both: use WHERE to narrow the rows that enter the grouping, then HAVING to keep only groups meeting a threshold. For instance, filter to this year's orders with WHERE, group by customer, then keep only customers whose total exceeds a limit with HAVING.
💡Filter Early
Put every row-level condition in WHERE, not HAVING. WHERE runs before grouping and shrinks the data early, which is faster than aggregating everything and discarding groups afterward.
5Logical Order of Execution
SQL clauses do not run in the order they are written. Understanding the logical execution order explains why WHERE cannot see aggregates and why column aliases sometimes fail in certain clauses. The database processes FROM first and SELECT nearly last.
The logical sequence is: FROM and JOIN gather rows, WHERE filters them, GROUP BY buckets them, aggregates and HAVING run on those buckets, then SELECT chooses columns, and finally ORDER BY sorts and LIMIT trims. This is why HAVING can reference SUM but WHERE cannot.
- FROM / JOIN # gather and combine source rows
- WHERE # filter individual rows
- GROUP BY # bucket rows into groups
- HAVING # filter groups on aggregate results
- SELECT # compute columns and aggregates
- ORDER BY / LIMIT # sort and trim the final output
6The GROUP BY Column Rule
Every column in the SELECT list must either be aggregated or listed in GROUP BY. If you select a column that is neither, the result is ambiguous because the group might contain many different values for it, and databases handle this differently.
Strict databases like PostgreSQL and SQL Server reject such queries outright. MySQL historically allowed them and returned an arbitrary value, which quietly produced misleading results. Always include non-aggregated select columns in GROUP BY to stay correct and portable.
DISTINCT vs GROUP BY
SELECT DISTINCT and GROUP BY with no aggregate both remove duplicates, but GROUP BY is intended for producing summaries. Reach for DISTINCT when you only want unique rows and for GROUP BY when you want per-group calculations.
7Common Mistakes to Avoid
Aggregation bugs are often silent — the query runs but the numbers are wrong. Watch for these.
- Selecting a non-aggregated column that is missing from GROUP BY, giving arbitrary values on lenient databases.
- Using WHERE to filter an aggregate — that condition belongs in HAVING.
- Forgetting that AVG and SUM ignore NULLs, which can skew results versus what you expect.
- Confusing COUNT(*) with COUNT(column); the latter skips NULLs and can undercount.
- Aggregating over a one-to-many join without realizing rows were duplicated, inflating SUM and COUNT.
⚠️Joins Inflate Aggregates
If you aggregate after joining a one-to-many relationship, each row on the one side is duplicated, so SUM and COUNT come out too high. Aggregate in a subquery before joining, or use COUNT(DISTINCT ...).
8Key Takeaways
Aggregations and GROUP BY power nearly every summary query you will write.
- Aggregate functions reduce many rows to one value; GROUP BY produces one summary per group.
- COUNT, SUM, AVG, MIN, and MAX are the core five; all but COUNT(*) skip NULLs.
- WHERE filters rows before grouping; HAVING filters groups after aggregation.
- Every non-aggregated SELECT column must be in GROUP BY.
- Beware one-to-many joins inflating aggregate totals.
9Frequently Asked Questions
Q: What is the difference between WHERE and HAVING? A: WHERE filters individual rows before grouping and cannot reference aggregate functions, while HAVING filters whole groups after aggregation and can test values like SUM or COUNT. Use WHERE for row conditions and HAVING for conditions on group summaries.
Q: What is the difference between COUNT(*) and COUNT(column)? A: COUNT(*) counts every row regardless of NULLs, while COUNT(column) counts only rows where that column is not NULL. If a column contains missing values, the two will return different numbers, so choose deliberately.
Q: Why do I get an error about a column not being in GROUP BY? A: Strict databases require every non-aggregated column in the SELECT list to appear in GROUP BY, because otherwise the value is ambiguous within a group. Add the column to GROUP BY, or wrap it in an aggregate function like MAX.
Q: Do aggregate functions include NULL values? A: With the exception of COUNT(*), aggregate functions ignore NULLs entirely. AVG divides by the count of non-NULL values, and SUM skips them, which can produce results that differ from a naive expectation if your data has missing values.
Related Reading
Get The Print Version
Download a PDF of this article for offline reading.
About the Publisher
SkillVeris Team
Data Science Team
Our data team shares real-world analytics, ML, and SQL insights grounded in industry practice.
View all postsRelated Posts
Never miss an update
Get the latest tutorials and guides delivered to your inbox.
No spam. Unsubscribe anytime.