GROUP BY in SQL: six common mistakes that distort totals

GROUP BY is one of the first clauses people learn in SQL and one of those that produce the most wrong reports. The reason is that an aggregation query almost always returns a believable-looking result: a neat table of numbers. There are no error messages warning you that those numbers do not answer the question asked. Here are the mistakes encountered most often, with how to recognise them.

How it works, briefly

A query with GROUP BY is processed in a precise logical order, different from the order in which it is written:

1. FROM and JOIN: the set of rows is built;
2. WHERE: individual rows are filtered;
3. GROUP BY: the remaining rows are divided into groups;
4. HAVING: groups are filtered;
5. SELECT: the result columns are computed;
6. ORDER BY: the result is sorted.

Keeping this sequence in mind explains most of the mistakes that follow.

Mistake 1: confusing WHERE and HAVING

WHERE acts on rows before grouping, HAVING on groups afterwards. To find customers who spent more than 1000 euros in 2025:

SELECT customer_id, SUM(total) AS spent
FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'
GROUP BY customer_id
HAVING SUM(total) > 1000;

The date filter goes in WHERE, because it concerns individual orders. The filter on the sum goes in HAVING, because the sum exists only after grouping. Putting a condition in HAVING that could go in WHERE usually works, but forces the database to group rows it will then discard.

Mistake 2: ungrouped columns in SELECT

SELECT customer_id, city, SUM(total)
FROM orders
GROUP BY customer_id;

If city is not in the GROUP BY and not inside an aggregate function, which value should the database show if a customer has orders shipped to different cities? The SQL standard forbids this query, unless the column is functionally dependent on the grouped key, and PostgreSQL and recent versions of MySQL reject it with an error. MySQL versions configured without the ONLY_FULL_GROUP_BY setting, however, accept it and return an arbitrary value from the group. The result can change from one run to the next without anyone noticing.

The fix is to add the column to the GROUP BY, if you want a group for each combination, or to choose explicitly which value to show, for example with MAX(city).

Mistake 3: counting rows instead of entities

An order_lines table has one row for each product in each order. The question «how many orders contain products in the Books category?»:

SELECT COUNT(*) FROM order_lines WHERE category = 'Books';

counts rows, not orders: an order with three books counts as three. The correct version is:

SELECT COUNT(DISTINCT order_id) FROM order_lines WHERE category = 'Books';

Before writing a count, always ask yourself: what does one row of this table represent?

Mistake 4: the average of averages

You want the average order value. A summary table already exists with the average for each shop:

Averaging the two averages gives 70, but the real average order value is (1000 × 40 + 10 × 100) / 1010, about 40.6. Shop B, with very few orders, weighs as much as shop A. The rule is: averages are recalculated from detail data, or from sums and counts, never by averaging other averages. The same applies to percentages.

Mistake 5: totals inflated by joins

You want revenue per customer together with the number of open support tickets:

SELECT c.id, SUM(o.total), COUNT(t.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN tickets t ON t.customer_id = c.id
GROUP BY c.id;

A customer with 4 orders and 3 tickets generates 12 rows before grouping: revenue comes out tripled and tickets quadrupled. The solution is to aggregate each table separately and then join the results:

WITH r AS (SELECT customer_id, SUM(total) AS revenue FROM orders GROUP BY customer_id),
     t AS (SELECT customer_id, COUNT(*) AS tickets FROM tickets GROUP BY customer_id)
SELECT c.id, COALESCE(r.revenue, 0), COALESCE(t.tickets, 0)
FROM customers c
LEFT JOIN r ON r.customer_id = c.id
LEFT JOIN t ON t.customer_id = c.id;

A good check: the report's grand total must match SELECT SUM(total) FROM orders.

Mistake 6: grouping by date on a column with a time

If order_date also contains the time, GROUP BY order_date creates a group for each distinct instant, not for each day. The «sales per day» report becomes a list of thousands of rows. You need to truncate the value to the day, with the function available in your database, such as CAST(order_date AS DATE) or date_trunc('day', order_date) in PostgreSQL, taking into account the time zone in which you want to define the day.

A related problem: days with no sales do not appear at all, because there are no rows to group. In a chart the trend looks continuous even when it is not. To show the zeros as well, you need a table or series of dates to join with a LEFT JOIN.

In short

• WHERE filters rows before grouping, HAVING filters groups afterwards.
• Every column in SELECT must be grouped or aggregated.
• COUNT(DISTINCT ...) counts entities when a row is not the entity.
• Averages are recalculated from sums and counts, never as an average of averages.
• Several one-to-many joins inflate totals; dates with times must be truncated to the day.