SQL HAVING vs WHERE Explained for Beginners (Examples)

What is the difference between HAVING and WHERE in SQL? Short answer: WHERE filters individual rows before they are grouped. HAVING filters groups after they are made. If you want to keep only the groups that pass a test, such as customers with more than two orders, you use HAVING.

This guide is for absolute beginners. We define every term, use one small table, and explain each query line by line. You should already know what SELECT and GROUP BY do, but we will recap them quickly.

The example table we will use

A table is a grid of data with rows and columns. Here is a table called orders. Each row is one order placed in an online shop.

CREATE TABLE orders (
  id INT,
  customer VARCHAR(50),
  country VARCHAR(50),
  amount INT
);

INSERT INTO orders (id, customer, country, amount) VALUES
(1, 'Ana', 'Spain', 40),
(2, 'Ben', 'Canada', 15),
(3, 'Ana', 'Spain', 60),
(4, 'Chen', 'China', 25),
(5, 'Ben', 'Canada', 30),
(6, 'Ana', 'Spain', 20),
(7, 'Dara', 'Kenya', 90);

The first statement creates the table with four columns. The second adds seven rows. The amount column is the price of each order in dollars. You can run this in any SQL tool, such as PostgreSQL, MySQL, or an online SQL playground.

Quick recap: WHERE filters rows

A row is one line of data. The WHERE clause is a condition that decides which rows to keep. Rows that do not match are thrown away.

SELECT *
FROM orders
WHERE amount > 30;

SELECT * means show every column. FROM orders says which table to read. WHERE amount > 30 keeps only rows where amount is bigger than 30. The result is three rows: orders 1, 3 and 7. WHERE looks at one row at a time.

Quick recap: GROUP BY makes groups

GROUP BY puts rows that share the same value into one group. You then use an aggregate function to calculate one number per group. An aggregate function is a function that works on many rows and returns one value. Common ones are COUNT, SUM and AVG.

SELECT customer, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer;

GROUP BY customer makes one group for each person. COUNT(*) counts the rows in each group. SUM(amount) adds up the amounts. AS gives a column a friendly name. The result has one row per customer: Ana has 3 orders and a total of 120, Ben has 2 and 45, Chen has 1 and 25, and Dara has 1 and 90.

Why WHERE cannot filter groups

Now suppose you want only customers with more than one order. You might try this:

SELECT customer, COUNT(*) AS order_count
FROM orders
WHERE COUNT(*) > 1
GROUP BY customer;

This gives an error. SQL runs WHERE before it makes groups, so at that moment there is no count yet. WHERE cannot use aggregate functions. This is the exact problem HAVING solves.

HAVING filters groups

HAVING is a condition that runs after GROUP BY. It can use aggregate functions like COUNT and SUM.

SELECT customer, COUNT(*) AS order_count
FROM orders
GROUP BY customer
HAVING COUNT(*) > 1;

First, GROUP BY customer makes the groups. Then HAVING COUNT(*) > 1 keeps only groups with more than one row. The result is Ana with 3 orders and Ben with 2. Chen and Dara are removed because they have only one order each.

HAVING vs WHERE: side-by-side

Using WHERE and HAVING together

You can use both in one query. WHERE cleans up the rows first. HAVING then filters the groups that are left.

SELECT customer, SUM(amount) AS total
FROM orders
WHERE amount >= 25
GROUP BY customer
HAVING SUM(amount) > 50
ORDER BY total DESC;

Step by step: WHERE amount >= 25 removes small orders (orders 2 and 6, worth 15 and 20, are removed; order 4, worth exactly 25, stays). GROUP BY customer groups the remaining rows. HAVING SUM(amount) > 50 keeps customers whose total is above 50. ORDER BY total DESC sorts from biggest to smallest. Dara (90) and Ana (100) pass, so Ana comes first.

The order SQL runs your query

You write SELECT first, but SQL thinks about the query in a different order. Knowing this makes HAVING and WHERE easy to remember.

1. FROM: pick the table.
2. WHERE: filter rows.
3. GROUP BY: make groups.
4. HAVING: filter groups.
5. SELECT: choose the columns to show.
6. ORDER BY: sort the result.

Common beginner mistakes

• Using WHERE with COUNT or SUM. Use HAVING instead.
• Using HAVING for a simple row filter. If the test is on a normal column such as country, WHERE is clearer and usually faster.
• Writing HAVING before GROUP BY. The correct order is WHERE, GROUP BY, HAVING, ORDER BY.
• Forgetting that a column in SELECT must be in GROUP BY or inside an aggregate function.

-- Better: filter rows with WHERE, not HAVING
SELECT country, COUNT(*)
FROM orders
WHERE country = 'Spain'
GROUP BY country;

Here the test is on the plain column country, so WHERE is the right tool. It removes rows early, so SQL has less work to do when it groups.

Practice ideas

• Find countries with a total amount above 40.
• Find customers whose average order is above 30 (use AVG).
• Find customers with at least 2 orders from orders over 20 dollars (WHERE plus HAVING).