Interactively build and visualize how SQL aggregates data by grouping rows based on specific columns.
Overview
The idea in brief
The GROUP BY clause in SQL is used with aggregate functions (COUNT, SUM, AVG, etc.) to group rows that have the same values in specified columns into summary rows. It's one of the most powerful tools for data analysis.
Source Table
SALES_DATA
Live Query
SELECT ... FROM SalesData;
Result Table
READY
Mastering SQL's GROUP BY Clause
Transform raw data into powerful summaries. This guide, paired with the interactive tool, will make you a `GROUP BY` expert.
The Core Idea: Collapse and Calculate
Imagine you have a table of sales data. You don't want to see every single sale; you want to know the total sales for each department. GROUP BY is how you do this. It performs two main steps:
Group (Collapse): It finds all the unique values in the column you specify (e.g., 'Department') and creates a "bucket" for each one. All rows matching that value are put into their respective bucket.
Aggregate (Calculate): For each bucket, it performs a calculation using an aggregate function on another column. For example, it will SUM() the 'Sales' for all rows within the 'Sales' department bucket.
The final result is a new, smaller table showing each group and its calculated aggregate value.
What "collapse and calculate" means in rows
GROUP BY takes many rows and returns one row per distinct value of the grouping columns. Everything else in the SELECT must therefore be either one of those grouping columns, or an aggregate that summarises the rows in the group.
Start with six sales rows:
Region
Salesperson
Amount
North
Alice
100
North
Bob
150
South
Carol
200
South
Dan
120
South
Alice
80
North
Carol
90
SELECT region, COUNT(*) AS sales, SUM(amount) AS total, AVG(amount) AS average
FROM sales
GROUP BY region;
region
sales
total
average
North
3
340
113.33
South
3
400
133.33
Six rows became two. The database sorted or hashed the rows by region, then ran each aggregate over each bucket.
Grouping by two columns makes one row per combination that exists in the data:
SELECT region, salesperson, SUM(amount)
FROM sales
GROUP BY region, salesperson;
That returns six rows here, because every pair happens to be unique. This is worth knowing: adding a column to GROUP BY can only increase the number of groups, never decrease it, and grouping by something unique (like a primary key) does nothing at all.
The rule that causes the error message
The most common GROUP BY error is selecting a column that is neither grouped nor aggregated:
SELECT region, salesperson, SUM(amount) -- salesperson is not grouped
FROM sales
GROUP BY region;
The database's objection is reasonable: the North group contains three different salespeople, so which one should it print? There is no defensible answer, so standard SQL refuses.
The fixes, in order of preference:
Add the column to GROUP BY, if you genuinely want finer groups.
Aggregate it — MAX(salesperson), or STRING_AGG(salesperson, ', ') to list them all.
Use a window function if you want the detail rows kept alongside the group total.
MySQL historically allowed this query and returned an arbitrary value, which is why old MySQL code often breaks when moved to PostgreSQL or when ONLY_FULL_GROUP_BY is enabled. The permissive behaviour was never a feature; it was a source of silently wrong reports.
COUNT, NULLs, and the counts that disagree
Three counts that look interchangeable and are not:
COUNT(*) — the number of rows in the group, full stop.
COUNT(column) — the number of rows where that column is not NULL.
COUNT(DISTINCT column) — the number of different non-NULL values.
For a group of 10 rows where 3 have a NULL email and the 7 remaining emails include one duplicate: COUNT(*) = 10, COUNT(email) = 7, COUNT(DISTINCT email) = 6.
The same NULL-skipping rule applies to every aggregate. AVG(amount) divides by the number of non-NULL amounts, not by the number of rows — so a column with missing values gives an average of what is present, which may or may not be what you meant. If NULL should count as zero, say so explicitly with AVG(COALESCE(amount, 0)).
SUM of an empty group returns NULL, not 0. Wrap it in COALESCE(SUM(amount), 0) whenever the result feeds a calculation or a display.
Grouping itself treats NULLs differently again: all NULLs in a grouping column are gathered into a single group, even though NULL = NULL is not true anywhere else in SQL. It is a deliberate exception, and it is why a stray NULL group appears in reports.
Grouping the same rows four different ways
GROUP BY collapses rows into buckets, and the only real difficulty is remembering that every selected column must either be grouped by or aggregated. This runs the same data through four groupings so you can watch the bucket definition change what comes out.
query.sqlSQLite
-- One row per customer: how many orders, and how much they spent.
--
-- Only three customers come back, not five: the inner JOIN drops
-- Dara and Eze, who have never ordered. A grouped query can only
-- ever report on rows that survived the joins above it.
--
-- Try grouping by o.status instead of c.name, or adding
-- p.category to both the SELECT and the GROUP BY.
SELECT c.name AS customer,
count(DISTINCT o.id) AS orders,
sum(i.qty) AS items,
round(sum(i.qty * p.price), 2) AS gross
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
GROUP BY c.id, c.name
ORDER BY gross DESC;
Result
Guided tour
Use the interactive tool above to see this process in action. The animation directly mirrors how a database engine thinks.
Total Sales per Department:
Set "Group By Column" to Department.
Choose the SUM() aggregate function.
Set "Target Column" to Sales.
Click Run Query. Watch as the animation highlights all rows for one department, fades the others, and then collapses them into a single summary row in the result table.
Number of Employees per Region:
Reset the query.
Set "Group By Column" to Region.
Choose the COUNT() aggregate function. (Notice the target column is disabled, as COUNT(*) just counts rows).
Click Run Query. The animation will now group by region and count the number of rows in each group.
Average Sales per Department:
Reset the query.
Set "Group By Column" to Department.
Choose AVG() and target Sales.
Run the query and compare the result to the SUM() experiment. You're now calculating the average sale amount within each department group, not the total.
The Rules of GROUP BY
When you use GROUP BY, your SELECT statement has a strict rule:
Any column in the SELECT list must either be part of the GROUP BY clause or be contained within an aggregate function.
-- This is ILLEGAL!
SELECT Department, Region, SUM(Sales) -- 'Region' is not in GROUP BY or an aggregate
FROM SalesData
GROUP BY Department;
-- This is LEGAL
SELECT Department, Region, SUM(Sales)
FROM SalesData
GROUP BY Department, Region; -- Group by all non-aggregated columns
This makes sense: if you've collapsed all 'Sales' department rows into one, which 'Region' should the database show? North? South? East? It's ambiguous. By grouping by both, you get a clear, unambiguous result for each Department-Region pair.
What to remember
GROUP BY reduces many rows into fewer summary rows.
It is almost always used with an aggregate function like SUM(), COUNT(), AVG(), MAX(), or MIN().
The columns in your SELECT statement must be either aggregated or listed in the GROUP BY clause.
Think of it as creating "buckets" for your data and then running a calculation on the contents of each bucket.
WHERE, GROUP BY, HAVING: the order settles the argument
The clauses run in a fixed order, and knowing it removes almost all confusion about which filter goes where:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
WHERE runs before grouping, so it filters individual rows and cannot see aggregates.
HAVING runs after grouping, so it filters whole groups and can use aggregates.
SELECT region, SUM(amount) AS total
FROM sales
WHERE sale_date >= '2026-01-01' -- drop old rows first
GROUP BY region
HAVING SUM(amount) > 300 -- then drop small regions
ORDER BY total DESC;
Two practical consequences. Put a condition in WHERE whenever it can go there — filtering before grouping means fewer rows to group, which is faster. And a condition on an aggregate can only go in HAVING, because the aggregate does not exist yet when WHERE runs.
The same ordering explains why SELECT aliases are usually unavailable in WHERE and HAVING (they are created later) but are available in ORDER BY (which runs later still). PostgreSQL and MySQL relax this for ORDER BY and GROUP BY; the standard does not.
Rollups, and grouping at several levels at once
Reports frequently need subtotals and a grand total alongside the detail. Writing three queries and stitching them together with UNION ALL works, and there is a purpose-built alternative:
SELECT region, salesperson, SUM(amount)
FROM sales
GROUP BY ROLLUP (region, salesperson);
That returns one row per salesperson within region, one subtotal row per region (with salesperson NULL), and one grand total row (both NULL). CUBE produces every combination of the grouping columns, and GROUPING SETS lets you list exactly the combinations you want.
Use GROUPING(column) to distinguish a genuine NULL in the data from a NULL that marks a subtotal row — it returns 1 for the latter.
Questions people ask
Can I GROUP BY a column not in SELECT? Yes. Grouping and selecting are independent; you might group by customer and select only the count.
Is GROUP BY slow? It requires sorting or hashing, so it costs more than a plain scan. An index on the grouping columns can let the engine skip the sort entirely, which is the main optimisation available.
What is the difference from DISTINCT?SELECT DISTINCT region and SELECT region GROUP BY region return the same rows and usually the same plan. GROUP BY is the one that lets you add aggregates.
Why does my average look wrong? Most often because NULLs are excluded from the denominator, or because a join duplicated rows before the grouping happened.
Can I use a window function instead? Often, and it is the right choice when you want group totals shown next to the individual rows rather than collapsing them.
How do I group by a computed value? Repeat the expression in GROUP BY — GROUP BY DATE_TRUNC('month', sale_date) — or wrap the query in a CTE and group by the alias.
Recap in one screen
GROUP BY collapses rows into one row per distinct combination of the grouping columns.
Every selected column must be grouped or aggregated, and the error message is telling the truth.
WHERE filters rows before grouping, HAVING filters groups after it.
ROLLUP and CUBE add subtotals without a pile of UNION ALLs.
Predict, then reveal
About to run: Total Sales per Department. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Recall check
0 of 3
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
Without scrolling back — what is the one-line takeaway from this module?
The clauses run in a fixed order, and knowing it removes almost all confusion about which filter goes where:
What does this module say about “The idea in brief”?
The GROUP BY clause in SQL is used with aggregate functions ( COUNT , SUM , AVG , etc.) to group rows that have the same values in specified columns into summary rows. It's one of the most powerful tools for data analysis.
What does this module say about “The Core Idea: Collapse and Calculate”?
Imagine you have a table of sales data. You don't want to see every single sale; you want to know the total sales for each department. GROUP BY is how you do this. It performs two main steps:
Cheat sheet
SQL GroupBy Visualizer
The GROUP BY clause in SQL is used with aggregate functions (COUNT, SUM, AVG, etc.) to group rows that have the same values in specified columns into summary rows. It's one of the most powerful tools for data analysis.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.