Guides › WHERE vs HAVING

WHERE vs HAVING in SQL Server: filter rows, then filter groups

WHERE throws rows away before you total them. HAVING throws groups away after. Use the wrong one and either the query fails or the total is of the wrong pile.

Customers over £1,000

SELECT customer_id, SUM(total_amount) AS spent
FROM Orders
WHERE status = 'Shipped'          -- drop rows first
GROUP BY customer_id
HAVING SUM(total_amount) > 1000  -- then drop groups
ORDER BY spent DESC;

WHERE status = 'Shipped' belongs on the row. HAVING SUM(total_amount) > 1000 can only exist after GROUP BY.

HAVING on a raw column

HAVING status = 'Shipped' without grouping by status is the 8120 family of pain. If it is not an aggregate and not in GROUP BY, it is a WHERE.

You cannot put SUM in WHERE

-- Fails
WHERE SUM(total_amount) > 1000

WHERE runs too early. The total does not exist yet. That is the whole distinction.

Try it on Harbour & Co

James Rowe (Sales Manager) needs this now: “Christmas hampers go to anyone who’s spent more than £1,000 with us. Who qualifies?” Write the query, run it, send the results. On the paid plan.

Take the ticket →

Learn it on a real ticket

Monday is free on WorkSQL. A colleague’s ticket lands, you write the T-SQL, we mark it. No videos. Five minutes.

Clock in free See Monday’s tickets