SQL GROUP BY and HAVING Explained With Examples

Sooner or later you need answers such as “how many orders did each customer place?” or “which products earned more than $1,000?” A plain SELECT lists rows, but these questions need summaries. That is where SQL GROUP BY HAVING comes in.

This tutorial explains both clauses step by step with a small orders table. All queries use standard SQL and work in PostgreSQL, MySQL, SQL Server and SQLite with minor differences.

If you are brand new to SQL, read our SQL tutorial for beginners first, then come back and run the examples.

Sample data for SQL GROUP BY HAVING

We will use an orders table.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer VARCHAR(50),
  product VARCHAR(50),
  amount DECIMAL(10,2),
  status VARCHAR(20)
);

INSERT INTO orders VALUES
(1, 'Asha',  'Keyboard', 50.00,  'paid'),
(2, 'Asha',  'Mouse',    20.00,  'paid'),
(3, 'Ravi',  'Monitor',  200.00, 'paid'),
(4, 'Ravi',  'Keyboard', 50.00,  'refunded'),
(5, 'Meera', 'Mouse',    20.00,  'paid'),
(6, 'Meera', 'Monitor',  200.00, 'paid'),
(7, 'Meera', 'Keyboard', 50.00,  'paid');

GROUP BY in SQL

GROUP BY splits rows into groups that share the same value in one or more columns. Aggregate functions then run once for each group.

SELECT customer, COUNT(*) AS orders_count, SUM(amount) AS total_spent
FROM orders
GROUP BY customer;

Seven rows became three, one per customer. Without GROUP BY, SUM(amount) would collapse the whole table into one row.

Common aggregate functions

• COUNT(*): number of rows in the group.
• SUM(col): total of a numeric column.
• AVG(col): average value.
• MIN(col) and MAX(col): smallest and largest values.

Grouping by more than one column

SELECT customer, product, COUNT(*) AS times_bought
FROM orders
GROUP BY customer, product;

Each unique customer and product pair becomes its own group.

SQL GROUP BY HAVING: the HAVING clause in SQL

The HAVING clause filters groups after the aggregate values are calculated. Use it when your condition involves an aggregate such as SUM or COUNT.

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
HAVING SUM(amount) > 100;

This returns only Ravi (250.00) and Meera (270.00), because Asha’s total of 70.00 does not pass the filter. You cannot write WHERE SUM(amount) > 100, because WHERE runs before any group exists.

SQL GROUP BY HAVING vs WHERE: which one filters what

You can use both in one query. Here we first ignore refunded orders with WHERE, then keep customers who spent over 100:

SELECT customer, SUM(amount) AS total_paid
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(amount) > 100
ORDER BY total_paid DESC;

Result: Meera with 270.00 and Ravi with 200.00 (his refunded 50.00 order was removed by WHERE). Filter with WHERE whenever you can, because it reduces the rows before grouping and is usually faster.

Logical order of execution

SQL does not run in the order you write it. A simplified view of the logical order is:

1. FROM and joins
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT

This explains why you can use ORDER BY total_paid but, in many databases, not a SELECT alias in HAVING. The PostgreSQL aggregate tutorial walks through the same idea, and the SQLite SELECT documentation describes how grouping is processed.

SQL GROUP BY HAVING with joins

Grouping becomes powerful when combined with joins, for example counting orders per customer name from a separate table. Our SQL joins explained guide covers the join types you will use first.

SELECT c.name, COUNT(o.id) AS orders_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
HAVING COUNT(o.id) = 0;

This finds customers who have never ordered. Counting o.id instead of * makes unmatched rows count as zero.

Common SQL GROUP BY HAVING mistakes

• Selecting a column that is not grouped. SELECT customer, product, SUM(amount) ... GROUP BY customer fails in most databases because product is neither grouped nor aggregated.
• Using WHERE for aggregates. Move conditions on SUM, COUNT or AVG to HAVING.
• Forgetting NULLs. COUNT(col) ignores NULL values while COUNT(*) does not, and NULLs form their own group.
• Putting all filters in HAVING. Row-level filters belong in WHERE for speed.

For testers, these queries are also great for data checks: find duplicate emails with GROUP BY email HAVING COUNT(*) > 1, or confirm totals match what the application shows.

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Final thoughts

Once you see that GROUP BY builds groups and HAVING filters them, SQL GROUP BY HAVING stops feeling tricky. Practise with your own tables: count, sum and average by different columns, then add HAVING to keep only the groups that matter. Combine it with joins and you can answer most reporting questions in a single query.

Frequently asked questions

Keep learning