HAVING vs WHERE in SQL Explained Simply

By /

WHERE and HAVING both filter rows, so beginners often wonder why SQL needs two of them. The answer is simple once you know when each one runs:

  • WHERE filters rows, before they are grouped.
  • HAVING filters groups, after GROUP BY has built them.
WHERE removes rows before GROUP BY; HAVING removes groups after GROUP BY, shown with a sales example

The example table

We’ll use a small Sales table:

SaleIDBranchProductAmount
1ManamaLaptop450
2ManamaMouse15
3ManamaMonitor180
4RiffaLaptop470
5RiffaMouse12
6MuharraqMouse14
7MuharraqKeyboard25

WHERE: filter rows before grouping

“Total sales per branch, but only count sales over 20”:

SELECT Branch, SUM(Amount) AS Total
FROM Sales
WHERE Amount > 20
GROUP BY Branch;
BranchTotal
Manama630
Riffa470
Muharraq25

The mouse sales were removed first, so they were never added to any total.

HAVING: filter groups after grouping

“Show only the branches whose total sales are over 400”:

SELECT Branch, SUM(Amount) AS Total
FROM Sales
GROUP BY Branch
HAVING SUM(Amount) > 400;
BranchTotal
Manama645
Riffa482

Muharraq’s total (39) was calculated, then the whole group was dropped because it failed the HAVING test.

Why you can’t use SUM() in WHERE

Try this:

SELECT Branch, SUM(Amount) AS Total
FROM Sales
WHERE SUM(Amount) > 400      -- error
GROUP BY Branch;

SQL Server answers: “An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list…”. MySQL gives a similar “Invalid use of group function” error.

It makes sense once you know the order SQL processes a query in. When WHERE runs, the groups don’t exist yet, so there’s no SUM to check.

The logical order of a SELECT query

StepClauseWhat happens
1FROM / JOINPick the tables and join them
2WHERERemove rows
3GROUP BYBuild groups
4HAVINGRemove groups
5SELECTCalculate the output columns and aliases
6ORDER BYSort the result
7TOP / LIMITKeep the first N rows

This order also explains why a column alias like Total can be used in ORDER BY but not in WHERE. The alias is created in step 5, after WHERE has already run. (MySQL bends the rule and lets you use an alias in HAVING. SQL Server does not, so repeat the expression: HAVING SUM(Amount) > 400.)

Using WHERE and HAVING together

“Branches whose laptop and monitor sales total more than 400”:

SELECT Branch, SUM(Amount) AS Total
FROM Sales
WHERE Product IN ('Laptop', 'Monitor')   -- rows first
GROUP BY Branch
HAVING SUM(Amount) > 400;                -- then groups
BranchTotal
Manama630
Riffa470

A common mistake: putting row filters in HAVING

-- Works, but wasteful
SELECT Branch, SUM(Amount) AS Total
FROM Sales
GROUP BY Branch
HAVING Branch <> 'Riffa';

-- Better
SELECT Branch, SUM(Amount) AS Total
FROM Sales
WHERE Branch <> 'Riffa'
GROUP BY Branch;

Both return the same result. The first one groups every row and then throws away a group. The second removes the rows early, and it can use an index on Branch. SQL Server’s optimizer is often smart enough to move simple filters like this for you, but don’t rely on it. Rule of thumb: if the condition doesn’t use an aggregate, put it in WHERE.

Other useful HAVING patterns

-- Customers with more than 5 orders
SELECT CustomerID, COUNT(*) AS Orders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 5;

-- Find duplicate values
SELECT Email, COUNT(*) AS Copies
FROM Customers
GROUP BY Email
HAVING COUNT(*) > 1;

-- Accounts where debits and credits don't balance
SELECT AccountNo
FROM JournalLines
GROUP BY AccountNo
HAVING SUM(Debit) <> SUM(Credit);

The “find duplicates” query is one I use all the time before creating a unique index or rewriting a subquery as a join.

WHERE vs HAVING at a glance

WHEREHAVING
FiltersIndividual rowsGroups
RunsBefore GROUP BYAfter GROUP BY
Can use SUM, COUNT, AVG…NoYes
Needs GROUP BY?NoUsually (without it, the whole result is one group)
Can use an indexYesNot for the aggregate condition

Related articles

Scroll to Top