LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 2520 min read

GROUP BY & HAVING

Learn how to group rows and compute per-group aggregates in SQL Server using GROUP BY, and filter those groups with HAVING.

Introduction

Aggregate functions become far more useful when applied per group rather than to an entire table. GROUP BY lets you answer questions like "how many orders did each customer place" or "what is the total revenue per month." This lesson covers grouping rows and filtering those groups using HAVING.

What You Will Learn
  • How GROUP BY organizes rows into groups for aggregation.
  • How to group by more than one column.
  • How to filter groups using HAVING.
  • The key difference between WHERE and HAVING.

Basic GROUP BY

GROUP BY collects rows that share the same value in a specified column into a single group, and any aggregate functions in the SELECT list are then computed per group rather than across the whole table.

Basic GROUP BY
SELECT CustomerName, COUNT(*) AS OrderCount, SUM(OrderTotal) AS TotalSpent
FROM Orders
GROUP BY CustomerName;
Result

Click Run to see what this code prints.

Every Non-Aggregated Column Must Be Grouped

Any column in the SELECT list that is not wrapped in an aggregate function must appear in the GROUP BY clause. Otherwise SQL Server raises an error, since it would not know which value to display for that column within each group.

Grouping by Multiple Columns

You can group by more than one column, which creates a separate group for each unique combination of those column values. This is useful for more granular breakdowns, such as revenue per customer per month.

Grouping by Multiple Columns
SELECT CustomerName, MONTH(OrderDate) AS OrderMonth, SUM(OrderTotal) AS MonthlyTotal
FROM Orders
GROUP BY CustomerName, MONTH(OrderDate);
Result

Click Run to see what this code prints.

Filtering Groups with HAVING

WHERE cannot filter on aggregate results, because it is evaluated before grouping happens. To filter groups based on an aggregated value — for example, only customers who spent more than 5000 total — you use HAVING, which is evaluated after GROUP BY has produced its groups.

HAVING
SELECT CustomerName, SUM(OrderTotal) AS TotalSpent
FROM Orders
GROUP BY CustomerName
HAVING SUM(OrderTotal) > 5000;
Result

Click Run to see what this code prints.

HAVING vs WHERE

WHERE and HAVING both filter rows, but at different stages of query processing. WHERE filters individual rows before they are grouped. HAVING filters entire groups after aggregation has already been computed. You can use both in the same query — WHERE to narrow down rows early, and HAVING to filter the resulting groups.

WHERE and HAVING Together
SELECT CustomerName, SUM(OrderTotal) AS TotalSpent
FROM Orders
WHERE OrderDate >= '2024-01-01'
GROUP BY CustomerName
HAVING SUM(OrderTotal) > 1000
ORDER BY TotalSpent DESC;
Result

Click Run to see what this code prints.

ClauseFiltersRuns
WHEREIndividual rowsBefore grouping
HAVINGGroups (often based on aggregates)After grouping

Common Mistakes

Avoid These Mistakes
  • Using WHERE to try to filter on an aggregate function result, which raises an error — use HAVING instead.
  • Selecting a non-aggregated column that is not included in the GROUP BY clause.
  • Forgetting that GROUP BY changes the granularity of the result set — you get one row per group, not per original row.
  • Using HAVING for a condition that could be expressed with WHERE, which is less efficient since WHERE filters rows before the more expensive grouping step.

Best Practices

  • Use WHERE to filter rows as early as possible, and HAVING only for conditions on aggregated values.
  • Include every non-aggregated selected column in the GROUP BY clause.
  • Use meaningful aliases for aggregate columns to make grouped reports easy to read.
  • Combine GROUP BY with ORDER BY to present grouped results in a sensible order.

Frequently Asked Questions

Yes, technically, though it is uncommon. Without GROUP BY, the entire result set is treated as a single group, so HAVING behaves like a WHERE clause applied to an aggregate over all rows.

Yes — use WHERE for the raw column condition and HAVING for the aggregate condition, as shown in the combined example above.

Not reliably. While some execution plans happen to return grouped results in a sorted order, you should always add an explicit ORDER BY if a specific order is required.

Key Takeaways

  • GROUP BY organizes rows into groups so aggregate functions compute per group.
  • Every non-aggregated selected column must appear in GROUP BY.
  • HAVING filters groups after aggregation; WHERE filters rows before it.
  • WHERE and HAVING can be combined in the same query.
  • GROUP BY can use multiple columns for more granular grouping.

Summary

GROUP BY and HAVING unlock powerful per-group reporting in SQL Server. Next, you will learn how to combine data across multiple tables using joins — one of the most important skills in relational databases.

Next Lesson →

Joins