SQL GROUP BY and HAVING Clause Explained with Examples

GROUP BY and HAVING are the backbone of SQL reporting — every "total sales by region" or "customers with more than 5 orders" query relies on them. They're also one of the most common sources of confusion for beginners, especially the difference between WHERE and HAVING. This guide clears it up with practical, runnable examples.

What GROUP BY Does

GROUP BY collapses multiple rows that share the same value in one or more columns into a single output row, so you can apply an aggregate function (SUM, COUNT, AVG, MIN, MAX) to each group separately.

SELECT region, SUM(order_total) AS total_sales
FROM orders
GROUP BY region
ORDER BY total_sales DESC;

Every column in the SELECT list that isn't wrapped in an aggregate function must appear in the GROUP BY clause — in standard SQL and PostgreSQL this is enforced; MySQL is more lenient by default but it's still best practice to follow this rule.

The Five Core Aggregate Functions

• COUNT(*) — number of rows in each group
• SUM(column) — total of a numeric column across the group
• AVG(column) — average value across the group
• MIN(column) — smallest value in the group
• MAX(column) — largest value in the group

SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(order_total) AS total_spent,
  AVG(order_total) AS avg_order,
  MIN(order_total) AS smallest_order,
  MAX(order_total) AS largest_order
FROM orders
GROUP BY customer_id;

WHERE vs HAVING — The Key Difference

WHERE filters individual rows before grouping happens, so it cannot reference an aggregate function. HAVING filters entire groups after GROUP BY and the aggregates have been calculated:

-- WHERE: filter rows before grouping (only completed orders count)
-- HAVING: filter groups after aggregation (only customers with 5+ orders)
SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(order_total) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) >= 5
ORDER BY total_spent DESC;

Trying to write WHERE COUNT(*) >= 5 will raise an error in every major database — COUNT(*) doesn't exist yet at the point WHERE is evaluated, because the rows haven't been grouped.

The Logical Order of Execution

SQL clauses are written in one order but executed in another. Understanding this order explains why WHERE can't see aggregates and HAVING can:

1. FROM — identify the source tables
2. WHERE — filter individual rows
3. GROUP BY — collapse remaining rows into groups
4. HAVING — filter groups based on aggregate values
5. SELECT — compute the final output columns
6. ORDER BY — sort the final result

Grouping by Multiple Columns

You can group by more than one column to get finer-grained subtotals — for example, sales per region per month:

SELECT
  region,
  MONTH(order_date) AS order_month,
  SUM(order_total) AS monthly_total
FROM orders
GROUP BY region, MONTH(order_date)
HAVING SUM(order_total) > 10000
ORDER BY region, order_month;

In PostgreSQL, replace MONTH(order_date) with EXTRACT(MONTH FROM order_date). Each unique combination of region and order_month becomes its own group.

Combining WHERE and HAVING in One Query

It's common — and efficient — to use both in the same query: WHERE trims down rows early (cheaper), and HAVING filters the smaller set of resulting groups:

SELECT product_id, AVG(rating) AS avg_rating, COUNT(*) AS review_count
FROM reviews
WHERE created_at >= '2025-01-01'
GROUP BY product_id
HAVING AVG(rating) >= 4.0 AND COUNT(*) >= 10
ORDER BY avg_rating DESC;

This finds well-reviewed products (average rating 4.0+) with enough reviews (10+) to be statistically meaningful, considering only reviews from 2025 onward.

Frequently Asked Questions