GROUP BY and HAVING
Group rows into buckets, aggregate each group, and filter groups with HAVING.
What you'll learn
- How GROUP BY partitions rows into groups
- Combining GROUP BY with aggregate functions
- Filtering groups with HAVING vs filtering rows with WHERE
- Grouping by multiple columns
- Understanding the logical order of SQL clauses
Concept
GROUP BY
GROUP BY takes the rows of a table and partitions them into groups that share the same value in the grouping column(s). You then apply an aggregate function to each group, getting one output row per group. For example, GROUP BY category on a products table produces one row per category, and you can compute COUNT(*) or AVG(price) for each.
WHERE vs HAVING
WHERE filters individual rows before grouping. HAVING filters groups after grouping and aggregation. This is a crucial distinction: use WHERE to exclude rows you do not want in any group, and HAVING to exclude groups based on their aggregate value. For example, "show categories with average price above 50" uses HAVING on AVG(price).
Grouping by Multiple Columns
You can group by more than one column. GROUP BY category, brand creates a group for every (category, brand) combination. The result has one row per unique combination. The order of columns in GROUP BY does not change the result, but it affects the default sort order in some databases.
Logical Clause Order
Although you write SQL as SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT, the database evaluates it in a different order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Understanding this explains why you cannot use column aliases from SELECT in WHERE or GROUP BY — they have not been computed yet at that stage.
Common Pattern: Top N per Group
A frequent reporting need is "the top 3 products by sales in each category." This requires either a window function (ROW_NUMBER() OVER (PARTITION BY ...)) or a self-join. It is a classic SQL interview question.
Example
-- Revenue and order count per category
SELECT
category,
COUNT(*) AS order_count,
SUM(total) AS revenue,
AVG(total) AS avg_order
FROM orders
WHERE status = 'completed'
GROUP BY category
ORDER BY revenue DESC;
-- Only categories with more than 50 orders
SELECT category, COUNT(*) AS num_orders
FROM orders
GROUP BY category
HAVING COUNT(*) > 50
ORDER BY num_orders DESC;
-- Group by two columns: category and month
SELECT
category,
DATE_TRUNC('month', created_at) AS month,
SUM(total) AS revenue
FROM orders
GROUP BY category, month
ORDER BY month, category;
Three patterns: basic GROUP BY with aggregates, HAVING to filter groups, and grouping by multiple columns including a date truncation.
Try it
- SQL Formatter
Format GROUP BY and HAVING queries for readability.
- CSV Formatter
Export grouped report data as CSV for spreadsheets.
Common mistakes
The mistake
Using HAVING for conditions that should be in WHERE
The fix
HAVING filters groups after aggregation. If the condition applies to individual rows, put it in WHERE — it is more efficient because rows are excluded before grouping.
The mistake
Selecting a column that is not in GROUP BY or aggregated
The fix
Every column in SELECT must either be in GROUP BY or wrapped in an aggregate function. Otherwise the database cannot decide which value to show for the group.
Practice GROUP BY and HAVING
12 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.