SQL GROUP BY combines rows that share values so aggregate functions can summarize them. HAVING filters the resulting groups, while WHERE filters individual rows before grouping. Understanding the order of these operations makes reporting queries easier to write and debug.
What GROUP BY does
Suppose an Orders table stores order_id, customer_id and amount. GROUP BY customer_id creates one summary group for each customer, allowing the query to calculate total order value or order count for each person.
Every selected column generally needs to be grouped or used inside an aggregate function, subject to the rules of the SQL database. The exact behavior should be checked for the database engine you use.
SELECT customer_id, SUM(amount) AS total_spend
FROM Orders
GROUP BY customer_id;COUNT, SUM and AVG with groups
COUNT(*) counts rows, SUM(amount) adds amounts, and AVG(amount) calculates the mean of non-NULL amounts. These can answer questions such as how many orders each customer placed or the average score by course.
Keep the meaning of each metric clear. COUNT(*) and COUNT(amount) can differ because COUNT(amount) excludes rows where amount is NULL.
SELECT course_code,
COUNT(*) AS enrollment_count,
AVG(score) AS average_score
FROM Results
GROUP BY course_code;WHERE versus HAVING
WHERE removes individual rows before grouping. HAVING removes groups after aggregate values have been computed. Use WHERE to filter raw records such as orders from a given year; use HAVING to keep customers whose total spending exceeds a specified amount.
Filtering rows early can reduce the data that needs to be grouped, but be careful not to exclude records that should contribute to the aggregate.
SELECT customer_id, SUM(amount) AS total_spend
FROM Orders
WHERE order_date >= '2026-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 500;Why aliases may not work in HAVING
Some database engines allow a SELECT alias in HAVING, while others require the aggregate expression itself. Writing HAVING SUM(amount) > 500 is a clear, portable starting point for many common SQL dialects, but syntax can still vary.
When a query returns unexpected groups, test the GROUP BY without HAVING first, inspect the aggregate values, then add the condition. That makes it easier to isolate whether the grouping or filter is the problem.
Common aggregation mistakes
Grouping by too many columns creates smaller groups than intended. Grouping only by a category when the desired summary is by category and month will combine all months together. Likewise, a JOIN before grouping can duplicate rows if the join relationship is one-to-many.
Verify totals against the source and explain the level of detail each output row represents. An aggregate report is only meaningful when the grouping level matches the business question.
- Decide what one output row should represent.
- Use WHERE for row-level conditions and HAVING for group-level conditions.
- Check duplicates created by joins before aggregating.
- Understand how NULL values affect COUNT and AVG.
- Compare aggregate totals with a known baseline where possible.
Understanding the concept is the first step.
Explore related resources or learn about subject-focused tutoring designed to help you understand the material.