SQL Server
WHERE vs HAVING in SQL Server
Understand when to use WHERE and HAVING in SQL Server: filter individual rows first, or filter groups after aggregation.
On this page
After learning to calculate aggregates and form groups with GROUP BY, you may want to keep only certain rows or groups. SQL Server has two different filters for these jobs: WHERE filters individual input rows before grouping; HAVING filters groups after they have been formed. This is the key distinction. It is also a practical extension of the logical processing order.
WHERE removes rows before grouping
Suppose you want to count orders by status, but only include orders of at least 100:
SELECT
Status,
COUNT(*) AS OrderCount
FROM dbo.Orders
WHERE Amount >= 100
GROUP BY Status;WHERE checks each order’s Amount first. The orders for 125.50, 310.00, and 199.00 remain; the other five rows, including the NULL Amount row, are removed from this query’s input. Then GROUP BY groups the remaining rows by Status. The result is two Complete orders and one Pending order, with no Shipped group. The underlying table is not changed. This is the row-level filtering introduced in the WHERE lesson.
HAVING removes groups after aggregation
Now ask which customers have placed more than one order. The condition concerns each customer’s order count, so first form groups and then filter them:
SELECT
CustomerId,
COUNT(*) AS OrderCount
FROM dbo.Orders
GROUP BY CustomerId
HAVING COUNT(*) > 1;SQL Server groups orders by CustomerId and calculates a count for each group. HAVING keeps groups whose count exceeds one. In this dataset, customers 1, 4, and 7 remain: Ana has 3 orders, David has 2, and Grace has 2 (one has a NULL Amount). Ben has one, so that group is removed. The query returns three rows, each representing one qualifying customer group.
- Orders
- WHERE — filters individual rows
- GROUP BY — creates groups
- HAVING — filters groups
- SELECT — returns columns
- ORDER BY — sorts results
An aggregate condition such as COUNT(*) > 1 cannot normally go in WHERE:
-- Invalid: the group count does not exist at the WHERE stage
SELECT CustomerId, COUNT(*) AS OrderCount
FROM dbo.Orders
WHERE COUNT(*) > 1
GROUP BY CustomerId;WHERE evaluates candidate rows before those groups and counts exist. Put the condition on the group in HAVING, as in the valid query above. This is a logical explanation; SQL Server’s physical execution details are a separate topic.
Use WHERE and HAVING together
A query can filter its input rows, group the survivors, and filter the groups:
SELECT
CustomerId,
COUNT(*) AS OrderCount,
SUM(Amount) AS Amount
FROM dbo.Orders
WHERE Status = N'Complete'
GROUP BY CustomerId
HAVING SUM(Amount) >= 100
ORDER BY Amount DESC;Read the logical sequence:
WHEREkeeps the fourCompleteorders.GROUP BYcreates one group per customer among those orders.COUNTandSUMcalculate each group’s measures.HAVINGkeeps groups whose completed-order total is at least 100.SELECTreturns the customer, count, and total columns.ORDER BYsorts those result rows by total, largest first.
Customers 1 and 4 qualify: Ana has 3 complete orders totaling 250.49; David has 1 totaling 199.00. The completed order for customer 1 includes 125.50, 49.99, and 75.00. Customer 4’s other order is Shipped, so it never enters this query’s groups. WHERE changes which rows can contribute to each count and sum; HAVING does not remove an order before the group aggregate is calculated.
A practical rule and a note about aliases
Use WHERE for conditions about row values, such as Status = N'Complete' or Amount >= 100. Use HAVING for conditions about a group or aggregate, such as COUNT(*) > 1 or SUM(Amount) >= 100. If a condition can be expressed as a row filter, placing it in WHERE usually states the intent more clearly because unwanted rows are excluded before grouping. This is a beginner-friendly way to choose the clause, not a promise about a particular execution plan.
Do not rely on a SELECT alias in HAVING just because it appears in the output. In the logical order from lesson 5, HAVING is processed before SELECT creates output aliases. Write HAVING SUM(Amount) >= 100, not HAVING Amount >= 100 when Amount is the alias for that aggregate. ORDER BY, which follows SELECT in the logical model, can use the selected alias.
SQL Server also permits HAVING in some aggregate queries without an explicit GROUP BY; the input is treated as a single group. That is a less common beginner pattern. For now, use GROUP BY when you need one result per category, then HAVING to keep qualifying groups.
Read each condition at the right level
Compare two questions that sound similar. “Keep orders whose amount is at least 100” is about individual orders. A row either meets that condition or does not, so place it in WHERE. “Keep customers whose combined order amount is at least 100” is about the total for a set of orders. No one order needs to reach 100 by itself; the group’s sum is what matters, so use HAVING after grouping by customer.
The distinction matters even when both queries return some of the same customers. Suppose a customer has two orders of 60. Filtering rows with WHERE Amount >= 100 removes both, so the customer is absent. Grouping both orders and applying HAVING SUM(Amount) >= 100 keeps the customer because the combined value is 120. The filters answer different business questions. Choose the clause by the meaning of the condition, not by which version happens to return the rows you expected in a quick test.
WHERE can use ordinary comparisons on columns available from the input tables, including comparisons to a constant, a range, or NULL checks such as IS NULL. It cannot refer to the eventual count or sum for a group because that result has not been formed yet. HAVING can test aggregate expressions and grouping values. For beginners, keep HAVING focused on groups and aggregates. While SQL Server permits some non-aggregate conditions there, moving a plain row condition into HAVING can make the query’s intent harder to follow.
In the combined example, every aggregate is calculated over the rows that survive WHERE. If you move Status = N'Complete' to a HAVING condition, SQL Server would first group all statuses for each customer and then retain or reject a whole customer group based on a group-level expression. That does not mean “only completed orders contribute.” A customer with completed and pending orders could have both statuses contribute to a total unless the grouping and condition were specifically designed otherwise. Filtering the input early makes the calculation’s population explicit.
This logical sequence also explains why an output alias is not always visible in every clause. SELECT constructs the output names after WHERE, GROUP BY, and HAVING in the logical model. An alias such as Amount for SUM(Amount) is a result label; it does not turn the aggregate into an input column or make it available to WHERE. Repeat the aggregate expression in HAVING so the condition clearly states what is being tested. ORDER BY comes later and can use a selected output alias, as shown in the combined query.
When reviewing a query, annotate the row population and group meaning in plain language. For example: “first, only Complete orders; next, one group per customer; then keep groups totaling at least 100.” If that sentence does not match the SQL clauses, the query is likely expressing a different question. This simple reading practice helps catch accidental changes when filters are added to an existing report.
One final check is to compare the filtered detail with the grouped output. Run the WHERE portion as a detail query first if the input population is unclear, then inspect which CustomerIds remain before aggregation. After adding GROUP BY, verify the count and sum for a known customer. For instance, Ana’s three Complete orders are 125.50, 49.99, and 75.00; their sum is 250.49. This small trace gives you evidence that both the row filter and the group calculation match the intended meaning. It is especially helpful when a query grows to include several filters, because a misplaced condition may still produce a syntactically valid result that answers the wrong question.
Common mistakes and practice
Avoid putting aggregate conditions in WHERE, using HAVING for ordinary row filtering, or assuming HAVING runs before GROUP BY. Remember that a WHERE condition changes the rows that contribute to every aggregate. Also avoid confusing the written order with logical processing, or expecting a SELECT alias to exist earlier than it does.
Practice with dbo.Orders:
- Count by status, considering only orders below 100.
- Find customers with more than one order.
- For
Pendingorders, show customers whose pending total is at least 100. - Explain the difference between filtering
Amount >= 100in WHERE and filteringSUM(Amount) >= 100in HAVING. - Why is
WHERE COUNT(*) > 1invalid?
Answers
-- 1: Complete 2, Shipped 1, Pending 1
SELECT Status, COUNT(*) AS OrderCount FROM dbo.Orders
WHERE Amount < 100 GROUP BY Status;
-- 2: customers 1, 4, and 7
SELECT CustomerId, COUNT(*) AS OrderCount FROM dbo.Orders
GROUP BY CustomerId HAVING COUNT(*) > 1;
-- 3: customer 2 has 310.00
SELECT CustomerId, SUM(Amount) AS PendingTotal FROM dbo.Orders
WHERE Status = N'Pending' GROUP BY CustomerId
HAVING SUM(Amount) >= 100;The first condition chooses individual orders before they enter groups. The second chooses completed group summaries. With that distinction in mind, the Summarizing Data stage is complete; continue with INSERT basics in the Changing Data Safely stage.
Check what you learned
2 quick questions
SQL Server Foundations
Lesson 15 of 25
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetQuery Store Quick ReferenceUse SQL Server Query Store to find regressions, compare plans, analyze runtime history, force or unforce plans safely, and understand when Query Store data can mislead you.Open cheat sheet
- ArticleAggregate Functions in SQL Server: COUNT, SUM, AVG, MIN, and MAXLearn how SQL Server aggregate functions summarize customer and order rows with COUNT, SUM, AVG, MIN, and MAX.Read article
- RoadmapSQL Server FoundationsUnderstand when to use WHERE and HAVING in SQL Server: filter individual rows first, or filter groups after aggregation.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.