SQL GROUP BY

The GROUP BY clause collapses many rows into fewer summary rows, one per unique value (or combination of values) in the columns you group by. It is the clause that turns a raw list of rows — every order, every employee, every sale — into an answer to a question like “how many employees are in each department?” or “what is the total revenue per customer?” You almost always use GROUP BY together with an aggregate function such as COUNT(), SUM(), AVG(), MIN(), or MAX(), because the aggregate function is what actually computes a value for each group.

Overview: How GROUP BY Works

When the database engine executes a query with GROUP BY, it does not process rows one at a time and print each one. Instead, it first determines every row that survives the WHERE clause, then it partitions those rows into buckets (‘groups’) based on the values in the GROUP BY column(s) — every row with the same combination of grouped values lands in the same bucket. Only after the buckets exist does the engine compute one output row per bucket, evaluating any aggregate functions (COUNT, SUM, AVG, and so on) across all the rows inside that bucket.

Internally, most database engines implement this in one of two ways. If there is a useful index on the grouped column(s), the engine can walk the index in sorted order and simply notice when the group value changes — this is very cheap because rows for the same group are already adjacent. If no such index exists, the engine typically builds a temporary structure (often a hash table keyed by the group values, or a sorted temporary B-tree) to collect matching rows together before aggregating. This is why grouping large, unindexed tables can be noticeably slower than grouping indexed ones — the engine is doing real sorting or hashing work behind the scenes.

A critical rule follows directly from this mechanism: every column in the SELECT list must either appear in the GROUP BY clause or be wrapped in an aggregate function. Once rows are collapsed into a group, the database has no single, well-defined value for a column that varies within that group — asking for name when you grouped by department is like asking “what is the one temperature of this entire week,” when the week had a different temperature every day. Some databases (like PostgreSQL and MySQL in strict mode) refuse to run such a query and raise an error; SQLite, by contrast, is permissive and will pick an arbitrary value — which is exactly why this is one of the most common sources of subtle, hard-to-spot bugs (see Common Mistakes below).

Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE row_filter_condition
GROUP BY column1
HAVING group_filter_condition
ORDER BY column1;

Clause	Purpose
SELECT column1, aggregate_function(column2)	Lists the grouped column(s) and any aggregate calculations to perform per group.
FROM table_name	The source table (or joined tables) supplying the rows.
WHERE row_filter_condition	Filters individual rows before grouping happens. Cannot reference aggregate functions.
GROUP BY column1	Defines which column(s) determine group membership. You can list multiple columns, comma-separated.
HAVING group_filter_condition	Filters entire groups after aggregation. This is where aggregate conditions like COUNT(*) > 1 belong.
ORDER BY column1	Sorts the final, already-grouped result set. Optional but recommended for predictable output.

Examples

Example 1: Counting rows per group

SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
ORDER BY department;

Result:

department	num_employees
Engineering	3
Marketing	1
Sales	2

The engine scans all six employee rows, buckets them by department, and counts how many rows fall in each bucket. Three buckets exist because there are three distinct department values, and ORDER BY department guarantees the buckets appear alphabetically rather than in whatever order the engine happened to build them.

Example 2: Combining multiple aggregate functions

SELECT department,
 ROUND(AVG(salary), 2) AS avg_salary,
 SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Result:

department	avg_salary	total_salary
Engineering	96333.33	289000
Sales	64500	129000
Marketing	58000	58000

You can compute several aggregates in the same query — each is calculated independently over the rows in each group. Here ROUND() is applied to AVG(salary) purely for display; the underlying calculation still uses the full-precision average.

Example 3: Filtering groups with HAVING

SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 1
ORDER BY department;

Result:

department	num_employees
Engineering	3
Sales	2

Marketing is excluded because it only has one employee, and HAVING COUNT(*) > 1 removes any group whose row count fails that test. Notice HAVING runs after grouping, so it can reference the aggregate COUNT(*) directly — something WHERE cannot do.

How It Works Step by Step (Under the Hood)

SQL reads like SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY, but the engine does not execute it in that written order. The real logical processing order is:

1. FROM — identify the source table(s), including any joins.
2. WHERE — discard individual rows that fail the condition, before any grouping.
3. GROUP BY — bucket the surviving rows by the grouping column(s).
4. Aggregate functions — compute COUNT, SUM, AVG, etc. for each bucket.
5. HAVING — discard entire groups whose aggregate result fails the condition.
6. SELECT — build the output columns (including aggregate values and aliases).
7. ORDER BY — sort the final set of group rows.
8. LIMIT — trim to the requested number of rows, if present.

This example threads the whole pipeline together:

SELECT department,
 COUNT(*) AS num_employees,
 ROUND(AVG(salary), 2) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY department
HAVING COUNT(*) >= 2
ORDER BY avg_salary DESC
LIMIT 1;

Result:

department	num_employees	avg_salary
Engineering	3	96333.33

Step by step: WHERE first removes the one employee hired before 2020, leaving five rows. GROUP BY then buckets those five into Engineering (3 rows) and Sales (2 rows) — Marketing has no remaining rows at all, so it never becomes a group. HAVING COUNT(*) >= 2 lets both surviving groups through. ORDER BY avg_salary DESC puts Engineering first, and LIMIT 1 keeps only that top row.

Common Mistakes

Mistake 1: Using an aggregate function inside WHERE

SELECT department, COUNT(*) AS num_employees
FROM employees
WHERE COUNT(*) > 1
GROUP BY department;

This fails because WHERE filters individual rows before grouping and aggregation happen — at that point in processing, COUNT(*) has not been computed yet, so SQLite raises a “misuse of aggregate function” error. The fix is to move the condition to HAVING, which runs after the groups (and their aggregate values) already exist:

SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

Mistake 2: Selecting a non-grouped, non-aggregated column

SELECT department, name, COUNT(*) AS num_employees
FROM employees
GROUP BY department;

This is dangerous precisely because SQLite lets it run without error, returning one arbitrary name per department (in practice, SQLite’s grouping implementation tends to keep the last row it processed for each group — here Frank for Engineering, Dave for Marketing, Eve for Sales — but this is an implementation detail, not something the SQL standard guarantees). Stricter engines like PostgreSQL, or MySQL running in ONLY_FULL_GROUP_BY mode, reject this query outright with an error instead of silently guessing. Either way, treat it as a bug: if you want the names, aggregate them explicitly, for example with GROUP_CONCAT() (SQLite/MySQL) or STRING_AGG() (PostgreSQL/SQL Server):

SELECT department,
 GROUP_CONCAT(name, ', ') AS employee_names,
 COUNT(*) AS num_employees
FROM employees
GROUP BY department
ORDER BY department;

Result:

department	employee_names	num_employees
Engineering	Alice, Carol, Frank	3
Marketing	Dave	1
Sales	Bob, Eve	2

Best Practices

• Only put non-aggregated columns in SELECT if they also appear in GROUP BY — don’t rely on a database’s permissiveness about “arbitrary” values.
• Filter rows as early as possible with WHERE rather than HAVING; it’s applied before grouping, so it reduces the work the engine has to do when building groups.
• Reserve HAVING strictly for conditions on aggregate results (COUNT, SUM, AVG, etc.) — anything that could be expressed with WHERE should be.
• Always pair GROUP BY with an explicit ORDER BY if row order matters to you; the SQL standard does not guarantee any particular ordering for grouped results.
• When grouping large tables, consider whether an index on the grouped column(s) exists — it can let the engine avoid an expensive sort or hash step.
• Use aliases (AS avg_salary) for aggregate expressions so results are readable and so you can reference the alias in ORDER BY.
• Remember that NULL values in the grouping column form their own group (a single “unknown” bucket) rather than being dropped.

Practice Exercises

Use the employees table from the examples above (Alice/Engineering/95000, Bob/Sales/62000, Carol/Engineering/105000, Dave/Marketing/58000, Eve/Sales/67000, Frank/Engineering/89000) to solve these:

1. Write a query that returns each department along with its total salary bill, sorted from highest total to lowest. Hint: use SUM() and ORDER BY ... DESC.
2. Write a query that lists only the departments whose average salary is above 60000. Expected result: Engineering and Sales qualify; Marketing (58000) does not.
3. Write a query that counts how many employees in each department were hired on or after 2021-01-01. Hint: filter with WHERE before grouping — think about which employees this removes first.

Summary

• GROUP BY collapses rows sharing the same value(s) in one or more columns into a single summary row per group.
• It is almost always used with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX(), which compute a value per group.
• The logical processing order is FROM → WHERE → GROUP BY → aggregates → HAVING → SELECT → ORDER BY → LIMIT.
• WHERE filters rows before grouping and cannot use aggregate functions; HAVING filters groups after aggregation and can.
• Every non-aggregated column in SELECT should appear in GROUP BY — otherwise the result is engine-dependent and unreliable.
• Always add an explicit ORDER BY when the order of grouped results matters.