GROUP BY & aggregates.
Summarize data. Answer business questions.
Every report, every dashboard, every analytics feature uses GROUP BY. "How many users signed up each month?" "What's the total revenue per country?" "Which products sell the most?" These aren't row-level questions — they're questions about groups of rows. That's what GROUP BY and aggregate functions answer. Combined with JOINs, they're how you turn raw data into insight. Every example has a Code / Output toggle so you see the actual grouped result.
Five aggregate functions cover every summary: COUNT, SUM, AVG, MIN, MAX. GROUP BY applies them per group. HAVING filters groups after aggregation (WHERE filters rows before). The rule: every column in SELECT must be in GROUP BY or wrapped in an aggregate. Query order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That order explains 90% of "why doesn't this work" questions.
Before this lesson: complete Lesson 3. GROUP BY combines with JOINs constantly in real queries.
📊 The five aggregate functions
Each one takes many rows and returns a single value. That's the entire concept.
An aggregate function operates on a set of rows and returns one value per group. Without GROUP BY, the whole table is one "group." With GROUP BY, you get one value per group. Every aggregate ignores NULL by default — SUM(amount) skips rows where amount is NULL.
-- Without GROUP BY: aggregate over the WHOLE table SELECT COUNT(*) AS total_orders, SUM(amount) AS total_revenue, AVG(amount) AS avg_order_value, MIN(amount) AS smallest_order, MAX(amount) AS largest_order FROM orders;
| total_orders | total_revenue | avg_order_value | smallest_order | largest_order |
|---|---|---|---|---|
| 5 | 785.49 | 157.10 | 45.50 | 300.00 |
Each function counts something different. COUNT(*) includes NULLs; COUNT(column) skips them. COUNT(DISTINCT column) counts unique values.
SELECT COUNT(*) AS total_rows, COUNT(email) AS rows_with_email, COUNT(DISTINCT country) AS unique_countries, COUNT(DISTINCT user_id) AS unique_customers FROM users;
| total_rows | rows_with_email | unique_countries | unique_customers |
|---|---|---|---|
| 8 | 6 | 5 | 8 |
COUNT
Counts rows. COUNT(*) counts all. COUNT(column) counts non-NULL values. COUNT(DISTINCT column) counts unique values. The most-used aggregate.
SUM
Adds up numeric values. Skips NULLs. SUM(amount) = total revenue. Works only on numeric columns — summing a text column errors or returns 0.
AVG
Averages numeric values. Skips NULLs — AVG(amount) divides by the count of non-NULL rows, not total rows. Useful for "average order value" and similar metrics.
MIN / MAX
Smallest / largest value. Works on numbers, dates, strings (alphabetical). MIN(created_at) = earliest signup. MAX(amount) = largest order.
COUNT() vs COUNT(column) matters. If a table has 100 rows and 20 have NULL email, COUNT() = 100, COUNT(email) = 80. If you're calculating "how many users have email," you want COUNT(email). If you're calculating "how many users total," you want COUNT(*). The distinction is easy to miss and produces wrong answers silently.
🗂️ GROUP BY — one row per group
The clause that turns aggregates from "whole table" into "per category."
-- Count users per country SELECT country, COUNT(*) AS user_count FROM users GROUP BY country ORDER BY user_count DESC;
| country | user_count |
|---|---|
| US | 3 |
| UK | 2 |
| ES | 1 |
| IN | 1 |
| DE | 1 |
Combined with JOINs, GROUP BY answers the questions every analytics dashboard needs:
-- Total spend and order count per user SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.amount), 0) AS total_spent FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.name ORDER BY total_spent DESC;
| name | order_count | total_spent |
|---|---|---|
| Marcus Lee | 2 | 345.50 |
| Alice Chen | 2 | 350.00 |
| Bob Smith | 1 | 89.99 |
| Priya Patel | 0 | 0 |
| Sofia García | 0 | 0 |
The rule
Every column in SELECT must be either (a) in GROUP BY, or (b) wrapped in an aggregate function. If you SELECT name but GROUP BY country, the database doesn't know which name to show — there are multiple names per country.
Why the rule exists
For each group, there's only one row in the output. Columns in GROUP BY have one value per group. Aggregates reduce many values to one. Anything else has multiple values per group — the database refuses to guess.
COALESCE for NULLs
COALESCE(SUM(amount), 0) returns 0 instead of NULL when there are no rows. Essential for LEFT JOIN + GROUP BY queries where some groups have no matching rows.
GROUP BY vs DISTINCT
SELECT DISTINCT country FROM users = unique countries. SELECT country FROM users GROUP BY country = same result. Use GROUP BY when you also want aggregates; DISTINCT when you only need unique values.
The "not in GROUP BY" error is the #1 GROUP BY mistake. SELECT name, COUNT(*) FROM users GROUP BY country fails because name isn't in GROUP BY and isn't aggregated. The database literally can't determine which name to show for each country. Fix: either include name in GROUP BY (giving one row per name+country combination) or wrap it in an aggregate like MAX(name) (picking an arbitrary name). For most queries, the intent is different — you probably meant GROUP BY name, not GROUP BY country.
🔗 Multiple GROUP BY columns
Group by combinations, not just single columns. Every pair becomes a group.
-- Group by country AND city — one row per (country, city) pair SELECT country, city, COUNT(*) AS user_count FROM users GROUP BY country, city ORDER BY country, user_count DESC;
| country | city | user_count |
|---|---|---|
| DE | Berlin | 1 |
| ES | Madrid | 1 |
| IN | Mumbai | 1 |
| UK | London | 2 |
| US | New York | 2 |
| US | San Francisco | 1 |
Combinations
GROUP BY a, b creates one group per unique (a, b) combination. Not "one per a AND one per b" — one per pair. Two columns × two values = up to 4 groups.
Order doesn't change the groups
GROUP BY country, city produces the same groups as GROUP BY city, country. Order only affects readability and (sometimes) sort order.
For hierarchical reports
"Sales by region and product" → GROUP BY region, product. "Users by signup year and country" → GROUP BY year, country. Multi-column GROUP BY is how you build drill-down reports.
Row count = unique combinations
With N unique pairs, you get N rows. Not N × 2, not N + something — exactly one row per unique combination of GROUP BY columns.
🔍 HAVING — filter groups
WHERE filters rows. HAVING filters groups. Both, but at different points.
-- Countries with more than 1 user SELECT country, COUNT() AS user_count FROM users GROUP BY country HAVING COUNT() > 1 ORDER BY user_count DESC; -- Combine WHERE and HAVING: -- WHERE filters rows BEFORE grouping (e.g., only active users) -- HAVING filters groups AFTER grouping (e.g., only large groups) SELECT country, COUNT() AS active_users FROM users WHERE active = true GROUP BY country HAVING COUNT() >= 2;
Result of first query: HAVING COUNT(*) > 1
| country | user_count |
|---|---|
| US | 3 |
| UK | 2 |
| Clause | Filters | When |
|---|---|---|
| WHERE | Individual rows | Before GROUP BY |
| HAVING | Groups | After GROUP BY |
You can't use WHERE to filter by an aggregate. WHERE COUNT() > 1 is a syntax error — COUNT doesn't exist yet when WHERE runs (grouping hasn't happened). Fix: use HAVING COUNT() > 1. The reverse also applies: HAVING runs after grouping, so you can't use HAVING to filter individual rows efficiently. Use WHERE for row-level filters, HAVING for group-level filters.
HAVING
Filters groups based on aggregate results. HAVING COUNT() > 100, HAVING SUM(amount) >= 1000. Runs after GROUP BY, so it can reference aggregates.
WHERE
Filters individual rows. Runs before GROUP BY. Cannot reference aggregate functions. WHERE country = 'US', WHERE created_at > '2026-01-01'.
Combine them
WHERE filters rows first, then GROUP BY groups the remaining rows, then HAVING filters groups. WHERE active = true GROUP BY country HAVING COUNT() >= 2 = "active users per country, only countries with at least 2 active users."
When in doubt
If your condition references an aggregate function (COUNT, SUM, AVG), use HAVING. Otherwise use WHERE. WHERE is more efficient — it reduces rows before aggregation, so the database does less work.
🏆 Top N reports — the killer pattern
The most-common dashboard query. GROUP BY + aggregate + ORDER BY + LIMIT.
-- Top 3 countries by user count SELECT country, COUNT(*) AS user_count FROM users GROUP BY country ORDER BY user_count DESC LIMIT 3; -- Top 3 users by total spent SELECT u.name, SUM(o.amount) AS total_spent FROM users u INNER JOIN orders o ON o.user_id = u.id GROUP BY u.name ORDER BY total_spent DESC LIMIT 3; -- Category totals with minimum threshold SELECT category, COUNT() AS product_count, AVG(price) AS avg_price FROM products WHERE stock > 0 GROUP BY category HAVING COUNT() >= 5 ORDER BY product_count DESC LIMIT 10;
Result of query 1: Top 3 countries by user count
| country | user_count |
|---|---|
| US | 3 |
| UK | 2 |
| ES | 1 |
The pattern
GROUP BY X → ORDER BY AGGREGATE DESC → LIMIT N. "Top N X by Y." Countries by user count, users by spending, products by sales, authors by book count.
Order by alias or expression
Both work: ORDER BY user_count DESC (alias) or ORDER BY COUNT() DESC (expression). Alias is usually cleaner and prevents mistakes.
INNER vs LEFT for aggregates
Use INNER JOIN when you only want users with orders. Use LEFT JOIN + COALESCE when you want all users including those with 0 orders. Different question, different join type.
Threshold with HAVING
Add HAVING COUNT() >= 5 to filter out tiny groups. Standard for "top 10 categories with at least 5 products" — prevents noise from categories with 1 item each.
This pattern is the entire "analytics" layer of most SaaS apps. MRR by plan, signups by month, revenue by country, top products by sales — all are GROUP BY something ORDER BY aggregate DESC LIMIT N. Once you internalize it, you can build a real analytics dashboard in an afternoon. The pattern is simple; recognizing when to apply it is the skill.
🏆 Challenge
Three tasks. Each exercises a different aggregate pattern.
Task 1 — Predict the output
Given a sales table with columns (id, product_id, quantity, price, sold_at) and 100 rows:
1. What does SELECT COUNT() FROM sales return?
2. What does SELECT SUM(quantity) FROM sales return?
3. What does SELECT product_id, COUNT() FROM sales GROUP BY product_id return — how many rows?
4. What does SELECT product_id, SUM(quantity) FROM sales GROUP BY product_id HAVING SUM(quantity) > 10 return?
5. What does SELECT product_id, SUM(quantity * price) AS revenue FROM sales GROUP BY product_id ORDER BY revenue DESC LIMIT 5 return?
For each query, predict the shape of the output.
Task 2 — Write analytics queries
Using a users table (id, name, country, created_at, active) and an orders table (id, user_id, amount, created_at):
1. Users per country, sorted descending
2. Countries with more than 10 users
3. Users who have placed more than 5 orders (JOIN + GROUP BY + HAVING)
4. Average order amount per user, excluding users who have no orders
5. Top 5 users by total spend, including users with 0 orders (LEFT JOIN + COALESCE)
6. Monthly signup counts (hint: GROUP BY DATE_TRUNC('month', created_at) in PostgreSQL)
7. Countries where the average order amount is over $100
Which of these require GROUP BY? HAVING? JOIN? All three?
Task 3 — Build a dashboard
You're building a dashboard for an e-commerce site. Given tables users, orders, products, order_items (order_id, product_id, quantity, unit_price), write the query for each KPI:
1. Total revenue this month
2. Number of orders this month
3. Average order value
4. Top 5 products by units sold this month
5. Top 5 products by revenue this month
6. Top 10 customers by total spend this year
7. Month-over-month revenue growth (hint: two grouped queries, then compare)
For each query, identify: which tables you join, which columns you GROUP BY, which aggregates you use, and any WHERE/HAVING filters. Then write the query.
This is the exact work of a real data analyst — the queries are the product.
⚠️ Common mistakes
The GROUP BY bugs that catch every beginner.
SELECT name, COUNT() FROM users GROUP BY country fails. Every SELECT column must be in GROUP BY or wrapped in an aggregate. Fix: include name in GROUP BY, or use MAX(name).
WHERE COUNT() > 1 is a syntax error. Fix: HAVING COUNT() > 1. WHERE runs before grouping; aggregates don't exist yet.
COUNT() counts rows. COUNT(email) counts non-NULL email values. If some are NULL, the results differ. Use the right one for your question.
Users with 0 orders disappear from COUNT queries because LEFT JOIN + GROUP BY with no COALESCE returns NULL for their sum. Fix: COALESCE(SUM(amount), 0) and use COUNT(o.id), not COUNT().
You wanted users per country but grouped by user_id — every user gets their own row. Fix: group by the column you actually want to aggregate by.
GROUP BY x LIMIT 5 returns 5 arbitrary groups. To get the top 5, add ORDER BY aggregate DESC. Without it, you get whichever 5 the database picked first.
SUM(name) errors or returns 0. SUM and AVG work on numbers only. MIN and MAX work on strings (alphabetical) and dates. COUNT works on anything.
AVG(amount) divides by non-NULL count, not total rows. If 5 rows have NULL amount, the average is higher than you might expect. Use AVG(COALESCE(amount, 0)) to include NULLs as 0.
➡️ What's next
You can summarize data. Next: modifying it.
❓ Frequently asked questions
What does GROUP BY do in SQL?
GROUP BY groups rows that share a value in one or more columns, so aggregate functions can be applied to each group instead of the whole table. SELECT country, COUNT() FROM users GROUP BY country returns one row per country with the count of users in each.
What are the five aggregate functions in SQL?
COUNT counts rows. SUM adds up numeric values. AVG averages numeric values. MIN returns the smallest value. MAX returns the largest value. All five ignore NULL by default.
What is the difference between WHERE and HAVING?
WHERE filters rows before GROUP BY. HAVING filters groups after GROUP BY. WHERE cannot reference aggregate functions; HAVING can. Use WHERE to filter rows, HAVING to filter groups.
What is the difference between COUNT() and COUNT(column)?
COUNT() counts all rows including NULLs. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts unique non-NULL values. Use COUNT() for "how many rows," COUNT(column) for "how many non-NULL values."
Why do I get a 'not in GROUP BY' error?
Because you SELECT a column that isn't in GROUP BY and isn't wrapped in an aggregate function. Fix: either include the column in GROUP BY, or wrap it in an aggregate like MAX() or MIN().
What is COUNT(DISTINCT column)?
COUNT(DISTINCT column) counts the number of unique non-NULL values in a column. If a column has values 1, 2, 2, 3, COUNT(column) returns 4 while COUNT(DISTINCT column) returns 3.
Can I GROUP BY multiple columns?
Yes. GROUP BY country, city groups by the combination of country and city. Each (country, city) pair gets one row in the result. This is common for hierarchical reports.
Can I sort groups by an aggregate?
Yes. ORDER BY works on aggregate results. SELECT country, COUNT(*) AS c FROM users GROUP BY country ORDER BY c DESC returns countries sorted by user count, descending.