SQL GROUP BY Explained: Counting, Summing and Grouping Rows

GROUP BY answers questions like "how many orders does each customer have?" or "how much revenue did we make each month?". It takes rows that share a value and collapses them into one row per group, so you can calculate something for each group.

We'll use this orders table:

id	customer	status	total	created_at
1	ada	paid	40	2026-09-02
2	ada	paid	25	2026-09-15
3	grace	refunded	60	2026-09-20
4	grace	paid	15	2026-10-01
5	linus	paid	80	2026-10-01

The basic pattern

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

customer	orders
ada	2
grace	2
linus	1

All of Ada's rows became one row, and COUNT(*) counted them.

Aggregate functions

These calculate one value from many rows:

Function	Gives
COUNT(*)	Number of rows
COUNT(column)	Number of non-NULL values (NULL in SQL)
COUNT(DISTINCT column)	Number of different values
SUM(column)	Total
AVG(column)	Average
MIN(column) / MAX(column)	Smallest / largest

SELECT customer,
       COUNT(*)   AS orders,
       SUM(total) AS spent,
       MAX(created_at) AS last_order
FROM orders
WHERE status = 'paid'
GROUP BY customer
ORDER BY spent DESC;

WHERE filters rows before grouping — here, only paid orders count.

Grouping by more than one column

SELECT customer, status, COUNT(*)
FROM orders
GROUP BY customer, status;

One row per combination: (ada, paid), (grace, refunded), (grace, paid), (linus, paid).

Grouping by date (revenue per month)

A very common report. In Postgres, date_trunc rounds timestamps down to the month:

SELECT date_trunc('month', created_at) AS month,
       SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY month
ORDER BY month;

Postgres lets you refer to the month alias in GROUP BY; some databases need the full expression repeated. (Dates and time zones)

Filtering groups: HAVING

To filter groups (not rows), use HAVING, which runs after grouping:

SELECT customer, SUM(total) AS spent
FROM orders
GROUP BY customer
HAVING SUM(total) > 50;

(WHERE vs HAVING)

The error everyone hits

ERROR: column "orders.status" must appear in the GROUP BY clause
or be used in an aggregate function

You asked for status but grouped only by customer. Grace has two orders with different statuses — which one should the single row show? The database refuses to guess.

Fix it by either:

• adding the column to GROUP BY (one row per customer and status), or
• wrapping it in an aggregate (MAX(status), COUNT(DISTINCT status)).

The rule: every column in SELECT must either be grouped or aggregated.

Order of operations

SQL runs a grouped query in this order, which explains most surprises:

1. FROM / JOIN — gather rows
2. WHERE — filter rows
3. GROUP BY — form groups
4. HAVING — filter groups
5. SELECT — compute output
6. ORDER BY / LIMIT

Beyond GROUP BY

If you want a total next to each row rather than collapsing rows — say, each order alongside the customer's total spend — that's a job for window functions. (Postgres window functions)

---

EasySpawn gives your app its own Postgres database and a terminal with Claude Code, so "how much did we make last month?" is one question away. See how it works or join the waitlist.

Related: SQL for Beginners · WHERE vs HAVING · SQL Joins Explained · NULL in SQL