1The sorting metaphor
Imagine a room full of 100 receipts. A WHERE clause throws away the unpaid receipts. A GROUP BY month tells you to take the remaining receipts and put them into 12 piles on the floor (one for each month). The SUM() tells you to use a calculator to add up the total of each specific pile. The ORDER BY tells you to arrange the piles from largest to smallest.
2Step-by-Step Breakdown
The Limitation. We know 'SELECT COUNT(*) FROM users' gives the total number of users. But what if the CEO asks: 'How many users do we have IN EACH COUNTRY?'
The GROUP BY Solution. The 'GROUP BY' clause splits the table into 'buckets' based on a column, and runs the aggregate function on EACH bucket independently.
Syntax. 'SELECT country, COUNT(*) FROM users GROUP BY country;'. The DB creates a bucket for 'Spain', counts the rows inside it, then a bucket for 'France', counts it, etc.
The Golden Rule. If you have a GROUP BY clause, every single column in your SELECT statement MUST either be inside an Aggregate Function (like SUM) OR listed in the GROUP BY clause. No exceptions.
Knowledge Check. Why does the query SELECT country, name, COUNT(*) FROM users GROUP BY country result in a fatal syntax error?
- →Because you cannot COUNT strings
- →Because 'name' is neither grouped by nor aggregated
Multiple Groupings. You can group by multiple columns. 'GROUP BY country, role'. This creates a bucket for 'Spain Admins', 'Spain Users', 'France Admins', etc.
Execution Order Revisit. Where does it fit? FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY. The WHERE clause filters the rows BEFORE they are put into the buckets.
Combining with WHERE. 'SELECT country, SUM(price) FROM orders WHERE status = 'paid' GROUP BY country;'. This calculates total revenue per country, but completely ignores unpaid orders.
Combining with ORDER BY. To find the most profitable country, sort the buckets. 'GROUP BY country ORDER BY SUM(price) DESC LIMIT 1;'.
Summary. GROUP BY is the engine behind every data analytics dashboard in the world.
Level Up 🚀
Advanced cheat sheets, SEO tricks, and interview prep for this topic.
Browser Support
Fully supported.
Fully supported.
Fully supported.
Fully supported.
Accessibility (A11y)
1Render Aggregated Results as Real Tables with Header Scope
Data returned by a GROUP BY query (e.g. country + count) should be rendered in the UI as a semantic <table> with <th scope="col"> for each aggregated column, not styled <div> rows, so screen reader users can navigate and understand which number belongs to which bucket.
SEO Implications
- 1
Aggregate Server-Side, Not in Client JavaScript, for Public Summary Pages
If a public page shows data grouped by category (e.g. 'Products by Brand'), run the GROUP BY in the database and render the summary server-side, so crawlers see the complete aggregated content immediately instead of an empty shell waiting on a client-side fetch.
Best Practices
Filter Raw Rows with WHERE Before They Reach GROUP BY
WHERE executes before rows are bucketed, so filtering unwanted rows there (e.g. status = 'paid') means GROUP BY only has to bucket and aggregate the rows you actually care about, which is cheaper than grouping everything and discarding buckets afterward with HAVING.
Every Non-Aggregated SELECT Column Must Be in the GROUP BY List
This is SQL's 'Golden Rule' for grouping: if a column isn't wrapped in an aggregate function like COUNT() or SUM(), it must appear in the GROUP BY clause, or the engine has no way to know which single value to display for that bucket.
Frequent Bugs
Query fails with an error like 'column "users.name" must appear in the GROUP BY clause or be used in an aggregate function'.
A non-aggregated column was added to SELECT without adding it to GROUP BY. Either wrap it in an aggregate (e.g. MIN(name)) if you just want a representative value, or add it to the GROUP BY list if you want a separate bucket per distinct value.
A developer tries to filter aggregated totals by writing WHERE SUM(price) > 1000 and gets a syntax or 'column does not exist' error.
WHERE runs before aggregation, so SUM(price) doesn't exist yet at that stage. Use HAVING SUM(price) > 1000 instead, which runs after GROUP BY has computed the totals.
Real-World Examples
Counting Users Per Country for an Admin Dashboard
A support dashboard needed a quick breakdown of how many registered users exist in each country, sorted by the largest markets first.
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
ORDER BY user_count DESC;