GROUP BY — aggregate per group

GROUP BY splits rows into groups by the values of one or more columns, then applies an aggregate function to each group.

Syntax

SELECT grouping_column, aggregate_function(col)
FROM table_name
GROUP BY grouping_column;

Example: headcount per department

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

One row is returned per distinct department_id.

Multiple grouping columns

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

The golden rule

Every column in SELECT must either be listed in GROUP BY or wrapped in an aggregate function. Non-grouped, non-aggregated columns are ambiguous — PostgreSQL will reject them.

-- ERROR: first_name not in GROUP BY and not aggregated
SELECT department_id, first_name, COUNT(*)
FROM employees
GROUP BY department_id;

Execution order

WHERE → GROUP BY → SELECT → ORDER BY

You can filter rows before grouping with WHERE, and filter groups after grouping with HAVING (next lesson).

Tips

• GROUP BY can use a column position number: GROUP BY 1 means the first column in SELECT.
• You can GROUP BY an expression: GROUP BY DATE_TRUNC('month', hire_date).