SQL GROUP BY Explained: Counting, Summing and Grouping Rows
GROUP BY collapses rows that share a value into one row per group, so you can count, sum or average each group. How it works with COUNT, SUM and AVG, grouping by several columns and by date, filtering with HAVING, and fixing the 'must appear in the GROUP BY clause' error.
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;
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:
FROM/JOIN— gather rowsWHERE— filter rowsGROUP BY— form groupsHAVING— filter groupsSELECT— compute outputORDER 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
Keep reading
What Is UTF-8? Character Encoding and Garbled Text (Mojibake) Explained
UTF-8 is the character encoding that lets computers store every language and emoji as bytes — and it's the default almost everywhere now. How encoding works, why you see garbled text like ’ or é, and how to fix it in HTML, databases, CSV files and code.
What Is Firebase? Google's App Backend Explained for Beginners
Firebase is Google's backend-as-a-service: a database, logins, file storage, hosting and serverless functions you use from your app without running a server. What each part does, how the free and pay-as-you-go plans work, how security rules protect your data, and when Firebase is (or isn't) the right fit.