GROUP BY

---

title: “GROUP BY” part: 1 topic_number: 12 slug: “group-by” difficulty: “Beginner” prerequisites: “aggregate-functions” —

GROUP BY

What Is It?

GROUP BY splits your data into categories, then calculates aggregates for each category separately. Instead of “What’s the average salary company-wide?”, you can ask “What’s the average salary in EACH department?”

Real-world analogy: Instead of calculating the total sales for your entire company, calculate total sales per region.

---

Syntax Breakdown

SELECT column, AGG_FUNCTION(column)
FROM table
WHERE conditions
GROUP BY column
ORDER BY column;

Breaking it down:

• GROUP BY column — Split rows into groups based on unique values in this column
• For each group, calculate the aggregate function
• Return one row per group

Critical rule: Every column in SELECT must either be:

1. In the GROUP BY clause, OR
2. Inside an aggregate function (COUNT, SUM, AVG, MIN, MAX)

---

Basic Example

Average salary by department:

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

What this does:

1. Groups employees by department_id
2. Calculates AVG(salary) for each group
3. Returns one row per department

Expected output:

department_id	avg_salary
1	101000.00
2	73666.67
3	73333.33
4	65000.00
5	90000.00
6	56500.00
7	105000.00
8	115000.00
9	NULL
10	98000.00
NULL	60000.00

Note: Department 9 shows NULL average (Noah has NULL salary). Paul (NULL department) also appears.

---

Going Deeper

Multiple Aggregates per Group

SELECT 
    department_id,
    COUNT(*) AS num_employees,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
ORDER BY department_id;

Expected output (partial):

department_id	num_employees	avg_salary	min_salary	max_salary
1	5	101000.00	88000.00	120000.00
2	3	73666.67	71000.00	78000.00
3	3	73333.33	67000.00	85000.00

Powerful insights! Now you see headcount AND salary stats per department.

GROUP BY with WHERE

Count employees per department, but only for those earning over $70K:

SELECT department_id, COUNT(*) AS high_earners
FROM employees
WHERE salary > 70000
GROUP BY department_id;

Order of execution:

1. WHERE filters employees (only salary > 70K)
2. GROUP BY splits filtered results by department
3. COUNT counts rows in each group

Expected output:

department_id	high_earners
1	5
2	2
3	1
5	1
7	1
8	1
10	1

GROUP BY Multiple Columns

Count employees by department AND manager:

SELECT department_id, manager_id, COUNT(*) AS team_size
FROM employees
GROUP BY department_id, manager_id
ORDER BY department_id, manager_id;

What this does: Groups by the COMBINATION of department and manager. Each unique pair gets its own group.

Expected output (partial):

department_id	manager_id	team_size
1	1	3
1	10	2
2	1	1
2	3	2

Interpretation:

• In dept 1, Alice (manager_id=1) manages 3 people
• In dept 1, Jack (manager_id=10) manages 2 people

Pause and Predict: What happens if you GROUP BY department_id but SELECT first_name (which isn’t aggregated)?

Answer

**Error:** `Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column` MySQL doesn't know which first_name to show when there are multiple employees per department. You must either: 1. Add first_name to GROUP BY (group by both) 2. Wrap first_name in an aggregate like COUNT 3. Remove first_name from SELECT

---

Watch Out — Common Mistakes

Mistake #1: SELECT Column Not in GROUP BY

--  WRONG
SELECT department_id, first_name, AVG(salary)
FROM employees
GROUP BY department_id;

Error: first_name isn’t in GROUP BY and isn’t aggregated. Which first_name should MySQL show for each department when there are multiple employees per department?

The fix:

-- • CORRECT — Remove first_name
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

-- • OR group by both (but this changes the meaning)
SELECT department_id, first_name, AVG(salary)
FROM employees
GROUP BY department_id, first_name;  -- Now groups by combination of dept+name

Mistake #2: WHERE vs HAVING (Filtering Groups)

--  WRONG — WHERE can't filter aggregates
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE AVG(salary) > 80000  -- ERROR!
GROUP BY department_id;

Error: Invalid use of group function

Why it fails: WHERE filters rows BEFORE grouping. Aggregates don’t exist yet!

The fix: Use HAVING (next topic!) to filter groups:

-- • CORRECT (preview of next topic)
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 80000;

Mistake #3: GROUP BY Order Matters (for Multiple Columns)

-- These produce different results!
GROUP BY department_id, manager_id
GROUP BY manager_id, department_id

Actually, in MySQL, the ORDER doesn’t change the results — the groups are the same. But the conceptual grouping is different:

• First groups by dept, then splits each dept by manager
• Second groups by manager, then splits each manager by dept

Best practice: List GROUP BY columns in logical order (usually matching your SELECT order) for readability.

---

Edge Case Spotlight

NULL in GROUP BY

SELECT manager_id, COUNT(*) AS direct_reports
FROM employees
GROUP BY manager_id;

Expected output includes:

manager_id	direct_reports
NULL	2
1	10
3	2
5	2
7	2
10	2

Key point: NULL is treated as its own group. Employees with NULL manager_id (Alice and Paul) are grouped together.

This is different from WHERE, where NULL comparisons need IS NULL. In GROUP BY, NULL values automatically group together.

---

Try This

Exercise 1 (Guided)

Count how many employees are in each department. Show department_id and the count. Sort by count (descending) to see which department is largest.

Hint

SELECT department_id and COUNT(*), GROUP BY department_id, ORDER BY your count column DESC.

Exercise 2 (Independent)

For each project, calculate the total number of employees assigned to it. Show project_id and the count. (Hint: Use the employee_projects table.)

Exercise 3 (Challenge)

Find the total budget per year for projects. Extract the year from start_date using YEAR() function, group by that year, and SUM the budgets. Sort by year.

Hint

Use YEAR(start_date) in both SELECT and GROUP BY. SUM(budget) for the total.

---

Answer Key

Exercise 1 Answer

```sql SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id ORDER BY employee_count DESC; ``` **Expected output (top 3):** | department_id | employee_count | |---------------|----------------| | 1 | 5 | | 2 | 3 | | 3 | 3 | Engineering has the most employees (5).

Exercise 2 Answer

```sql SELECT project_id, COUNT(*) AS employee_count FROM employee_projects GROUP BY project_id ORDER BY employee_count DESC; ``` **Expected output (partial):** | project_id | employee_count | |------------|----------------| | 1 | 3 | | 2 | 3 | | 3 | 2 | | ... | ... | Shows how many employees are assigned to each project.

Exercise 3 Answer

```sql SELECT YEAR(start_date) AS project_year, SUM(budget) AS total_budget FROM projects GROUP BY YEAR(start_date) ORDER BY project_year; ``` **Expected output:** | project_year | total_budget | |--------------|--------------| | 2020 | 450000.00 | | 2021 | 410000.00 | | 2022 | 1830000.00 | | 2023 | 1095000.00 | **Note:** Projects with NULL budget are excluded from SUM. The year 2023 includes "Employee Training Portal" which has no start date... actually, checking the data, all projects have start dates. This shows budget per year based on when projects started.

---

Quick Recap

• GROUP BY splits data into categories and calculates aggregates per category
• Every SELECT column must be in GROUP BY OR inside an aggregate function
• WHERE filters rows before grouping; HAVING filters groups after (next topic)
• Can GROUP BY multiple columns for sub-categories
• NULL values form their own group
• ORDER BY should reference columns in SELECT list

---

Up Next

Time for a Challenge! → Mini Challenge 4

You’ve learned aggregation and grouping! Test your skills with a challenge before moving to HAVING.