GROUP BY & HAVING

1. Definition

GROUP BY is used to group rows that have the same values in specified columns into summary rows.

It is typically used with aggregation functions to compute results per group instead of the whole table.

Example use cases:

SELECT column_name, AGG_FUNCTION(column) FROM table_name GROUP BY column_name;

Employee
---------
id
name
salary
dept_id

Sample data:

id	name	salary	dept_id
1	A	50000	10
2	B	60000	10
3	C	55000	20
4	D	70000	20

Find average salary per department.

SELECT dept_id, AVG(salary) FROM employee GROUP BY dept_id;

Result:

dept_id	avg_salary
10	55000
20	62500

SQL execution order (simplified):

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Steps:

Grouping can be done using multiple columns.

Example:

SELECT dept_id, manager_id, COUNT(*) FROM employee GROUP BY dept_id, manager_id;

This creates groups based on the combination of both columns.

HAVING is used to filter grouped results.

Example:

SELECT dept_id, COUNT(*) FROM employee GROUP BY dept_id
HAVING COUNT(*) > 5;

Only departments with more than 5 employees will appear.

Example:

Using WHERE

SELECT dept_id, COUNT(*) FROM employee WHERE salary > 50000 GROUP BY dept_id;

First filters rows → then groups.

Using HAVING

SELECT dept_id, COUNT(*) FROM employee GROUP BY dept_id HAVING COUNT(*) > 5;

Groups first → then filters groups.

Rule:

Every column in SELECT must be either:

Invalid query:

SELECT dept_id, name, AVG(salary) FROM employee GROUP BY dept_id;

Reason:

Correct version:

SELECT dept_id, AVG(salary) FROM employee GROUP BY dept_id;

Results can be sorted.

Example:

SELECT dept_id, COUNT(*) FROM employee GROUP BY dept_id ORDER BY COUNT(*) DESC;

Departments with the highest employee count appear first.