What is the difference between the HAVING and WHERE statements

Question

What is the difference between the HAVING and WHERE statements in SQL Server

Rajesh Gami · Answer

1 WHERE Clause The WHERE clause filters rows before any grouping or aggregation happens. You cannot use aggregate functions (like SUM , COUNT , AVG ) directly in a WHERE clause. It is used in SELECT, UPDATE, DELETE statements to filter raw data. Example: Suppose we have a table Sales : SaleID Product Quantity Price 1 Apple 10 5 2 Banana 20 2 3 Apple 15 5 4 Orange 8 6 Query: Get all sales where Quantity > 10 SELECT * FROM Sales WHERE Quantity > 10; Result: SaleID Product Quantity Price 2 Banana 20 2 3 Apple 15 5 ? Filters rows before any aggregation . 2 HAVING Clause The HAVING clause filters groups or aggregated data . It is used with GROUP BY . You can use aggregate functions in HAVING because it works after grouping. Example: Find products with total quantity sold > 20 SELECT Product, SUM(Quantity) AS TotalQuantity FROM Sales GROUP BY Product HAVING SUM(Quantity) > 20; Result: Product TotalQuantity Apple 25 Banana 20 ? Filters after aggregation . Key Differences Feature WHERE HAVING Purpose Filters rows Filters groups Works With Non-aggregated columns only Aggregated values allowed Order of Execution Before GROUP BY After GROUP BY Can Use Aggregate ? No ? Yes Combined Example: Get products with price > 4 and total quantity sold > 20: SELECT Product, SUM(Quantity) AS TotalQuantity FROM Sales WHERE Price > 4 -- Filters individual rows first GROUP BY Product HAVING SUM(Quantity) > 20; -- Filters grouped result

Sreenath Kappoor · Answer

WHERE and HAVING both filter rows WHERE filters rows before grouping/aggregation. HAVING filters groups after aggregation. Example in MS Sql Server, CREATE TABLE Sales ( Region VARCHAR(20), Product VARCHAR(20), Amount INT ) INSERT INTO Sales (Region, Product, Amount) VALUES ('East', 'A', 100), ('East', 'B', 200), ('West', 'A', 150), ('West', 'B', 250), ('North', 'A', 300), ('South', 'B', 100) -- Returns individual rows where Amount > 150. SELECT Region, Product, Amount FROM Sales WHERE Amount > 150 -- Returns only regions where total sales exceed 300. SELECT Region, SUM(Amount) AS TotalSales FROM Sales GROUP BY Region HAVING SUM(Amount) > 300 -- Both Where and Having -- Only rows with Product='A' are considered. -- Groups with SUM(Amount) > 100 are kept. SELECT Region, SUM(Amount) AS TotalSales FROM Sales WHERE Product = 'A' -- filter rows first GROUP BY Region HAVING SUM(Amount) > 100; -- then filter groups

Sandhiya Priya · Answer

Here’s the clear and simple difference between WHERE and HAVING — with examples so you understand exactly when to use each. WHERE vs HAVING (Quick Answer) Feature WHERE HAVING Applies To Row-level filtering before grouping Group-level filtering after grouping Used With Any SELECT (with or without GROUP BY) Only used with GROUP BY or aggregates Can use Aggregate Functions? NO (SUM, COUNT, AVG not allowed) YES Filters Individual rows Aggregated results When to Use WHERE? Use WHERE when you want to filter rows BEFORE grouping/aggregation . Example: SELECT * FROM Sales WHERE Amount > 5000; Filters each row No grouping required Cannot use SUM(), COUNT(), AVG() inside WHERE. When to Use HAVING? Use HAVING when you want to filter grouped or aggregated results . Example: SELECT CustomerId, SUM(Amount) AS TotalAmount FROM Sales GROUP BY CustomerId HAVING SUM(Amount) > 5000; Filters based on aggregated value Works only after GROUP BY Can use SUM(), COUNT(), AVG() Easy Example to Understand the Difference Scenario: Find customers whose total purchase amount is greater than 10,000. This will NOT work (aggregate in WHERE): SELECT CustomerId, SUM(Amount) FROM Sales GROUP BY CustomerId WHERE SUM(Amount) > 10000; SQL Error: “Cannot use aggregate function in WHERE clause.” Correct way (use HAVING): SELECT CustomerId, SUM(Amount) FROM Sales GROUP BY CustomerId HAVING SUM(Amount) > 10000; Use BOTH WHERE and HAVING Together Example: Find customers from "Chennai" whose total purchase is above 10,000. SELECT CustomerId, SUM(Amount) AS Total FROM Sales WHERE City = 'Chennai' -- row-level filter GROUP BY CustomerId HAVING SUM(Amount) > 10000; -- group-level filter Summary WHERE = filters rows before grouping HAVING = filters groups after aggregation If your condition uses aggregates , use HAVING If your condition is for individual rows , use WHERE

What is the difference between the HAVING and WHERE statements

What is the difference between the HAVING and WHERE statements in SQL Server

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (3)