GROUP BY Basics: Aggregating Rows Into Summaries

Overview

GROUP BY collapses many rows that share a common value into a single summary row, so you can pair it with aggregate functions like COUNT and SUM to answer questions like "how many orders has each customer placed, and how many total items did they buy?" Instead of scanning a raw orders table row by row, you get one row per customer with the numbers already rolled up.

This is the foundation of almost every reporting query: totals per customer, counts per category, revenue per day, and so on all start from the same GROUP BY + aggregate pattern.

The Query

Step-by-Step Breakdown

1. FROM orders — start from the raw, one-row-per-order table.
2. GROUP BY customer_id — bucket all rows into groups that share the same customer_id. Every customer with at least one order gets exactly one group.
3. COUNT(*) — within each group, count how many order rows fell into it — the customer's total number of orders.
4. SUM(quantity) — within each group, add up the quantity column across all of that customer's orders.
5. ORDER BY order_count DESC, customer_id — sort the summarized rows, not the original ones. Once you GROUP BY, ORDER BY operates on the aggregated result set.

Notice that SELECT only lists customer_id and aggregate expressions. Every column that isn't wrapped in an aggregate function must appear in the GROUP BY clause — that's the core rule to internalize.

Variations

Add a HAVING clause to keep only customers with more than 2 orders:

HAVING filters groups after they've been aggregated, which is why it can reference COUNT(*) directly. WHERE can't do this — it only sees individual rows, before grouping happens, so it has no concept of a group's total count yet.

Common Mistakes

• Selecting a non-aggregated, non-grouped column. SELECT customer_id, order_date, COUNT(*) ... GROUP BY customer_id fails (or gives an arbitrary value) in most databases because order_date isn't unique per group and isn't in the GROUP BY list.
• Using WHERE when you meant HAVING. WHERE COUNT(*) > 2 is invalid SQL — aggregates aren't available until after grouping, which is exactly what HAVING exists for.
• Forgetting COUNT(*) counts rows, not distinct values. If a customer could have duplicate order rows for some reason, COUNT(*) still counts each one; use COUNT(DISTINCT column) when you specifically need unique values.
• Grouping by the wrong granularity. Grouping by customer_id alone loses per-product detail; if you need per-customer-per-product totals, you'd GROUP BY customer_id, product_id instead.