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.

By JaviPublished 12 min readBeginner

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:

SQL query: WHERE removes rows before groupingT-SQL
SELECT
    Status,
    COUNT(*) AS OrderCount
FROM dbo.Orders
WHERE Amount >= 100
GROUP BY Status;
T-SQL6 linesRead-only query display. It is not connected to a database.

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:

SQL query: HAVING removes groups after aggregationT-SQL
SELECT
    CustomerId,
    COUNT(*) AS OrderCount
FROM dbo.Orders
GROUP BY CustomerId
HAVING COUNT(*) > 1;
T-SQL6 linesRead-only query display. It is not connected to a database.

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.

  1. Orders
  2. WHERE — filters individual rows
  3. GROUP BY — creates groups
  4. HAVING — filters groups
  5. SELECT — returns columns
  6. ORDER BY — sorts results
WHERE filters rows before grouping; HAVING filters groups after

An aggregate condition such as COUNT(*) > 1 cannot normally go in WHERE:

SQL query: HAVING removes groups after aggregation (2)T-SQL
-- 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;
T-SQL5 linesRead-only query display. It is not connected to a database.

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:

SQL query: Use WHERE and HAVING togetherT-SQL
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;
T-SQL9 linesRead-only query display. It is not connected to a database.

Read the logical sequence:

  1. WHERE keeps the four Complete orders.
  2. GROUP BY creates one group per customer among those orders.
  3. COUNT and SUM calculate each group’s measures.
  4. HAVING keeps groups whose completed-order total is at least 100.
  5. SELECT returns the customer, count, and total columns.
  6. ORDER BY sorts 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:

  1. Count by status, considering only orders below 100.
  2. Find customers with more than one order.
  3. For Pending orders, show customers whose pending total is at least 100.
  4. Explain the difference between filtering Amount >= 100 in WHERE and filtering SUM(Amount) >= 100 in HAVING.
  5. Why is WHERE COUNT(*) > 1 invalid?

Answers

SQL query: AnswersT-SQL
-- 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;
T-SQL10 linesRead-only query display. It is not connected to a database.

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

Question 1 of 2Which clause filters rows before grouping?
Question 2 of 2Which clause filters groups after aggregation?

SQL Server Foundations

Lesson 15 of 25

Continue to next lesson

INSERT Basics in SQL Server

Continue to Lesson 16

Tags

  • SQL Server
  • SQL Fundamentals
  • SSMS
  • T-SQL