Grouping Data — SQL

GROUP BY column makes one group for each different value in that column, and the aggregate functions calculate per group: the result is one row per group…

The aggregate functions from the previous lesson summarise a whole table into one result row. On the employees table, that is 8 people with an average salary of $7,300. But HR (the team that manages employees) usually asks something else: how many people are in each department, and what is their average salary?

For that question there is GROUP BY. GROUP BY gathers the rows that have the same value in one column into one group. Then the aggregate functions (COUNT, AVG, SUM, and others) calculate per group, not over the whole table. Picture a pile of payslips on the HR desk, with one slip for each row of the table. The slips are sorted by department into three piles, and each pile is one group. Then each pile is counted: how many slips it holds, and what the average salary is. HAVING is the step after that: remove the piles that hold fewer than three slips.

Without GROUP BY: one big group

The employees table in the Lab and the exercise holds 8 people:

id name department salary
1 Budi IT 8000
2 Siti IT 9000
3 Andi IT 8500
4 Dewi HR 6000
5 Rizky HR 6500
6 Maya HR 6200
7 Dimas Marketing 7000
8 Citra Marketing 7200

The query below uses aggregate functions without GROUP BY:

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees;
department headcount avg_salary
IT 8 7300

As in the Aggregate Functions lesson, all 8 rows become one group, so the result is 1 row. The IT in the department column is just a value SQLite took from one of the rows, not a summary of the 8 rows. MySQL and PostgreSQL even refuse this query.

GROUP BY: one row per group

Now the same query gets GROUP BY department:

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
department headcount avg_salary
HR 3 6233.333333333333
IT 3 8500
Marketing 2 7100

HAVING: filter the groups

To filter the groups, add HAVING after GROUP BY. The query below keeps only the departments with more than 2 people:

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;
department headcount avg_salary
HR 3 6233.333333333333
IT 3 8500

HAVING checks each group after that group has been counted. Marketing has only 2 people, so the Marketing group is dropped. IT and HR are left.

WHERE or HAVING

Guess first: does SELECT department, COUNT(*) FROM employees WHERE COUNT(*) > 2 GROUP BY department; run?

No. SQLite works through a query in this order, which is not the order you write it in: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. When WHERE runs, the groups don't exist yet, so COUNT(*) can't be calculated. That's why SQLite refuses with misuse of aggregate: COUNT(). A condition that uses an aggregate function goes in HAVING.

WHERE and HAVING may be used together:

SELECT department, COUNT(*) AS headcount
FROM employees
WHERE salary > 6300
GROUP BY department
HAVING COUNT(*) > 2;

WHERE first drops the rows of Dewi (6,000) and Maya (6,200), so HR has only 1 person left. After that, HAVING lets only IT through, the only group with more than 2 people.

💡 SQLite, MySQL and PostgreSQL differ here

⚠️ Conditions in the wrong place, columns that stray

TL;DR: GROUP BY column makes one group for each different value in that column, and the aggregate functions calculate per group: the result is one row per group. WHERE filters rows before they are grouped, and HAVING filters groups afterwards. A condition that uses an aggregate function, such as COUNT or AVG, always goes in HAVING. A plain column in SELECT (one that is not inside an aggregate function) must be in GROUP BY.

⚡ One row per group

Write:

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Meaning: GROUP BY department gathers the rows with the same department into one group. There are three different values, so there are three groups: IT, HR, Marketing. The aggregate functions COUNT and AVG are calculated inside each group, so the result is 3 rows, one per group.

⚡ HAVING: filter the groups

Write:

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

Meaning: HAVING filters groups: it checks each group after that group has been counted. The condition here is COUNT(*) > 2, and Marketing has only 2 people, so the Marketing group is dropped.

⚡ WHERE or HAVING

Guess first: WHERE runs before the rows are grouped. If WHERE COUNT(*) > 2 is written before GROUP BY, does the query run?

No, it's refused with misuse of aggregate: COUNT(). When WHERE runs, the groups don't exist yet, so COUNT(*) can't be calculated.

Meaning: WHERE filters rows before they're grouped, HAVING filters groups afterwards. A condition with an aggregate function always goes in HAVING.

⚡ Columns in SELECT

Meaning: A plain column in SELECT, which is a column that is not inside an aggregate function, must be in GROUP BY. A column that is not in GROUP BY must be written inside an aggregate function, like salary in AVG(salary). When this rule is broken, SQLite does not refuse, but MySQL and PostgreSQL refuse.

What you will learn