SQL GROUP BY and HAVING explained with examples

GROUP BY splits rows into groups that share a value and runs an aggregate like SUM once per group. HAVING then keeps only the groups that pass a test on those aggregates.

Total orders per customer

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
  ('Ana', 20.00), ('Ben', 15.50), ('Ana', 35.00),
  ('Cleo', 9.99), ('Ben', 40.00), ('Ana', 12.50);

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

Output:

customer  orders  total
--------  ------  -----
Ana       3       67.5
Ben       2       55.5
Cleo      1       9.99

SQLite sorts the six rows into one pile per customer, then counts and adds up each pile. Each result row is one group. Every column in SELECT must either appear in GROUP BY or sit inside an aggregate such as COUNT or SUM.

Filter groups with HAVING

To see only customers who spent more than 50 in total, add HAVING:

SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING SUM(amount) > 50;

Output:

customer  total
--------  -----
Ana       67.5
Ben       55.5

Cleo's group is dropped because her total is 9.99.

WHERE and HAVING do different jobs:

• WHERE filters rows before grouping, so use it for conditions on columns, such as WHERE amount > 10.
• HAVING filters groups after grouping, so use it for conditions on aggregates, such as SUM(amount) > 50.

You can use both in one query. Write them in this order: WHERE, GROUP BY, HAVING, ORDER BY.

Common mistakes

Putting an aggregate in WHERE. This fails, because WHERE runs before any totals exist:

SELECT customer, SUM(amount) FROM orders
WHERE SUM(amount) > 50
GROUP BY customer;

SQLite reports a misuse of aggregate error. Move the condition to HAVING.

Selecting a column that isn't grouped. SELECT id, customer, SUM(amount) ... GROUP BY customer gives each customer several ids to choose from. Most databases refuse it, and SQLite quietly picks one, which looks right and isn't. Remove the column or add it to GROUP BY.

Takeaway

Use GROUP BY to get one summary row per group, WHERE to filter rows before grouping, and HAVING to filter groups after it.