SQL GROUP BY and HAVING: Aggregate Correctly

Why GROUP BY matters for reporting

Raw transaction tables are long and flat. The Orders table in a typical shop holds one row per purchase: order_id, customer_id, amount, region, order_date. A stakeholder never asks for the 48,213rd row. They ask for revenue per region, or the count of orders per customer, or the average ticket size per month. GROUP BY is the clause that turns those 48,000 rows into the 12 summary rows a manager actually reads. Without it you are stuck exporting to a spreadsheet and pivoting by hand, which is slow and error prone.

The mental model that helped me: GROUP BY builds a separate bucket for each distinct value in the column you name, then runs your aggregate function inside each bucket. One bucket for region 'West', one for 'East', and so on. The aggregate ignores the original row level and only reports the bucket result.

The basic GROUP BY syntax

SELECT region, SUM(amount)
FROM Orders
GROUP BY region

If you add customer_id to SELECT you must also add it to GROUP BY, or the query will error on most engines.

SELECT region, SUM(amount), COUNT(order_id), AVG(amount)
FROM Orders
GROUP BY region

SELECT region, SUM(amount)
FROM Orders
WHERE order_date >= '2025-01-01'
GROUP BY region

gives you only this year's totals and runs faster.

SELECT region, SUM(amount) AS revenue
FROM Orders
GROUP BY region
ORDER BY revenue DESC

The aggregate functions you will actually use

SELECT product_id, SUM(quantity)
FROM Sales
GROUP BY product_id

shows total units sold per product.

WHERE vs HAVING: the split that confuses everyone

This is the single most common GROUP BY error. WHERE runs before the grouping, so it can only test single-row values. HAVING runs after the grouping, so it can test the aggregate result. You cannot write WHERE SUM(amount) > 5000 because at the WHERE stage the sum does not exist yet.

SELECT customer_id, SUM(amount)
FROM Orders
WHERE region = 'West'
GROUP BY customer_id

The WHERE checks each row's region column directly.

SELECT customer_id, SUM(amount) AS total
FROM Orders
GROUP BY customer_id
HAVING SUM(amount) > 5000

HAVING sees the finished total for each customer.

SELECT customer_id, SUM(amount) AS total
FROM Orders
WHERE order_date >= '2025-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 5000
ORDER BY total DESC

HAVING with multiple conditions

SELECT customer_id, SUM(amount) AS total, COUNT(order_id) AS orders
FROM Orders
GROUP BY customer_id
HAVING SUM(amount) > 5000 AND COUNT(order_id) > 10

SELECT region, AVG(amount) AS avg_ticket
FROM Orders
GROUP BY region
HAVING AVG(amount) > 200 OR COUNT(order_id) > 5000

Common mistakes that break GROUP BY

SELECT customer_id, region, SUM(amount)
FROM Orders
GROUP BY customer_id

If a customer appears in two regions the engine cannot pick one region, so it errors. Either add region to GROUP BY or drop it from SELECT.

Worked example: top-spending customers report

Here is a complete, runnable query against the Orders table. It returns customers who spent more than $5,000 in 2025, with their order count and average ticket, sorted biggest first.

SELECT customer_id,
  SUM(amount) AS total_spend, COUNT(order_id) AS order_count, AVG(amount) AS avg_ticket
FROM Orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 5000
ORDER BY total_spend DESC;

GROUP BY vs window functions

GROUP BY reduces many rows to one per group, so you lose the detail. If you need both the per-row detail and the group total side by side, a window function like SUM(amount) OVER (PARTITION BY region) keeps every row. Reach for GROUP BY when you only want the summary, and for window functions when you want the summary attached to each original row. The two solve different problems and are covered in our SQL window functions and CTEs guide.

Grouping by more than one column

You can group by two or more columns to get a finer breakdown. GROUP BY region, product_id returns one row per region-product pair, which is how you build a matrix report that a manager can read across and down. The order of columns in GROUP BY sets the rollup order, not the visual sort order, so do not confuse it with ORDER BY.

When you add a second grouping column, every aggregate is computed inside that combined bucket. A SUM(amount) under GROUP BY region, category sums within each region-category cell, not across the whole region. I recommend listing the broad dimension first, such as region, and the narrow one second, such as product_id, so the result reads top-down and matches how people scan a report.

SELECT region, product_id, SUM(amount) AS revenue
FROM Orders
GROUP BY region, product_id
ORDER BY region, revenue DESC

Before you ship a grouped query, run it on a small date range and check the row count against a manual total. I once shipped a region report that doubled the West numbers because I grouped by region but summed a column that already carried a region multiplier; the fix was a five-minute spot check. A quick COUNT(*) on the source and a hand-added total for one region catches most of these errors before anyone sees the dashboard, and it takes less time than explaining a wrong number later.

Next steps and a quick checklist

Start with one grouping column and one aggregate, confirm the numbers against a manual count of ten rows, then add HAVING. Keep WHERE for row-level filters and HAVING for group-level filters. Pair this with date aggregation when your groups are time periods, and with CASE WHEN when you need to label or split groups before summing. Practice on the Orders or Sales table until the WHERE and HAVING split feels automatic. When your groups are time periods, read SQL date aggregation, and when you need to label rows before summing see SQL CASE WHEN. For conditional totals across segments, our advanced aggregate guide builds on the same ideas.