SQL tutorial · Intermediate

GROUP BY

Intermediate

⏱️ 12 mins read

Groups the employees table (Topic 1) and the orders table (Topic 7) — including the special group that NULL keys form.

What You'll Learn

GROUP BY groups rows with identical values into summary rows. Every column in SELECT must either appear in GROUP BY or be wrapped in an aggregate function. You can group by multiple columns to get unique combinations. Execution order: WHERE filters rows → GROUP BY groups → HAVING filters groups.

Syntax

SELECT col, AGG(col2) FROM table
GROUP BY col;

SELECT col1, col2, AGG(col3) FROM table
GROUP BY col1, col2;

Example

-- Headcount per department
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id;

-- Revenue per customer per month (MySQL syntax)
SELECT
  customer_id,
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  COUNT(*) AS orders,
  SUM(total) AS revenue
FROM orders
GROUP BY customer_id, DATE_FORMAT(order_date, '%Y-%m');

Beyond the Basics

Data in play — reference tables for this topic

-- employees (from Topic 1)
-- id | name    | department_id | salary
-- 1  | Ada     | 1             | 145000
-- 2  | Grace   | 1             | 115000
-- 3  | Alan    | 2             | 92000
-- 4  | Edsger  | NULL          | 78000
-- 5  | Barbara | 3             | 64000

-- orders (from Topic 7)
-- id | customer_id | status    | total  | order_date
-- 1  | 1           | shipped   | 240.00 | 2024-01-15
-- 2  | 2           | shipped   | 89.90  | 2024-02-03
-- 3  | 1           | pending   | 430.00 | 2024-03-11
-- 4  | 3           | shipped   | 59.50  | 2024-04-02
-- 5  | 4           | cancelled | 120.00 | 2024-05-20

One row per group — and NULL is a group of its own

GROUP BY department_id over our five employees produces FOUR groups, not three: dept 1 (Ada, Grace), dept 2 (Alan), dept 3 (Barbara) — and one group keyed NULL, where Edsger lands. All NULL keys are grouped together, even though NULL = NULL is UNKNOWN in a WHERE clause. Grouping and comparison follow different rules.

SELECT department_id, COUNT(*) AS headcount, MAX(salary) AS top_sal
FROM employees
GROUP BY department_id;
-- 1    | 2 | 145000
-- 2    | 1 | 92000
-- 3    | 1 | 64000
-- NULL | 1 | 78000   <- Edsger's group; IS NOT NULL will never see him

After any GROUP BY, scan the output for a NULL-keyed row — that is your missing-data bucket, and reports often need to label it (Topic 7's COALESCE).

GROUP BY without aggregates is DISTINCT (Topic 6)

SELECT department_id FROM employees GROUP BY department_id returns exactly what SELECT DISTINCT department_id does: one row per value, NULL included once. Knowing they are equivalent ends the 'which should I use' debate — GROUP BY exists to power aggregates; reserve DISTINCT for pure deduplication.

-- These return identical results:
SELECT DISTINCT department_id FROM employees;
SELECT department_id FROM employees GROUP BY department_id;
-- 1, 2, 3, NULL — four rows either way

Same plan in most engines. Choose by intent: DISTINCT says 'unique values', GROUP BY says 'per-group numbers' — future readers decode intent, not just results.

Group by an expression — but group by the SAME expression

Monthly revenue groups by the month-part of order_date. The rule: every non-aggregated SELECT item must appear in GROUP BY, and when it is an expression, the GROUP BY must carry that same expression — SELECT and GROUP BY run at different stages of the pipeline (Topic 2), so the engine will not infer the match for you.

SELECT
  DATE_FORMAT(order_date, '%Y-%m') AS month,
  SUM(total) AS revenue
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;
-- 2024-01 | 240.00
-- 2024-02 | 89.90
-- 2024-03 | 430.00
-- 2024-04 | 59.50
-- 2024-05 | 120.00

Expression in SELECT, same expression in GROUP BY — some engines also accept the alias, but repeating the expression is the only portable form.

Common Mistakes

MySQL (with ONLY_FULL_GROUP_BY disabled) may allow non-grouped columns in SELECT — PostgreSQL and SQL Server do NOT. Always follow the rule: every SELECT column must be in GROUP BY or aggregated.

Interview Tips

Know that GROUP BY can be used without aggregate functions — it acts like DISTINCT. Also: grouping by an expression (DATE_FORMAT, YEAR()) is common in analytics. Mention that NULL values are grouped together.

Official References

Test Yourself — 6 questions

Self-check · 0/6 answered

1. GROUP BY department_id over ShopCo's 5 employees produces how many groups?

2. What does GROUP BY without any aggregate function do?

3. The GROUP BY rule for SELECT columns is…?

4. Which is the portable way to group by a month expression?

5. Where does Edsger land in a GROUP BY department_id query?

6. Why did my GROUP BY query return groups in a 'random' order?

Practice

For each department, return the department_id and the average salary.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Can I GROUP BY a column alias?

MySQL and PostgreSQL allow it; SQL Server does not. The portable habit is to repeat the full expression in GROUP BY — exactly what we did with DATE_FORMAT above. (ORDER BY alias is universally safe, because it runs after SELECT.)

Why did my GROUP BY query lose its row order?

Grouping produces an unordered set of groups — any perceived order is coincidence. If presentation order matters (reports almost always do), add an explicit ORDER BY, ideally on an aggregate like SUM(total) DESC.

Aggregate Functions

HAVING Clause

Back to Full Roadmap