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 |
GROUP BY departmentmakes one group for each differentdepartmentvalue: IT, HR, Marketing.COUNT(*)andAVG(salary)are calculated inside each group. For IT: (8,000 + 9,000 + 8,500) / 3 = 8,500.- The result is one row per group, so 3 rows.
- A plain column in
SELECT, which is a column that is not inside an aggregate function (heredepartment), must be a column inGROUP BY. A column that is not inGROUP BYmust be written inside an aggregate function, likesalaryinAVG(salary).
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
WHEREfilters rows before they are grouped.HAVINGfilters groups after they are counted.
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
- A column that isn't grouped.
SELECT department, name, COUNT(*) FROM employees GROUP BY department;runs in SQLite: thenamecolumn holds one name from each group (Dewi, Budi, Dimas). MySQL (5.7 and later, with its default settings) and PostgreSQL refuse it. PostgreSQL's message:column "employees.name" must appear in the GROUP BY clause or be used in an aggregate function. - An alias in HAVING.
HAVING headcount > 2(using the aliasheadcount) works in SQLite and MySQL, but PostgreSQL refuses it (column "headcount" does not exist).HAVING COUNT(*) > 2is safe everywhere. - The order of the groups. SQLite happens to return the groups in alphabetical order (HR, IT, Marketing). That order isn't guaranteed, so write
ORDER BYwhen the order matters.
⚠️ Conditions in the wrong place, columns that stray
- An aggregate function in WHERE. Refused with
misuse of aggregate: COUNT(). Move the condition toHAVING. - HAVING without GROUP BY. The whole table becomes one group. In the exercise, if
GROUP BYis missing butHAVING COUNT(*) > 2is there, the result is 1 row (IT | 8), not 2. - A column in SELECT that isn't in GROUP BY. SQLite does not refuse it, and fills that column with a value from one of the rows in the group. Other databases refuse.
- Clause order.
GROUP BYis written afterWHERE,HAVINGafterGROUP BY, andORDER BYafter that.
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.