PostgreSQL "column must appear in the GROUP BY clause or be used in an aggregate function"

1. Aggregate the column

If you want every name in the department’s row, combine them:

SELECT d.name,
       count(*) AS employees,
       string_agg(e.first_name, ', ' ORDER BY e.first_name) AS names
FROM departments d
JOIN employees e ON e.department_id = d.id
GROUP BY d.name
ORDER BY d.name;

array_agg gives an array instead of text; min() / max() give one value (the alphabetically first name, the highest salary).

2. Group by the column too

If you want one row per department and per value — for example, how many people per department and job title — the column belongs in GROUP BY:

SELECT d.name, e.job_title, count(*) AS employees
FROM departments d
JOIN employees e ON e.department_id = d.id
GROUP BY d.name, e.job_title
ORDER BY d.name, e.job_title;

3. Group by the primary key

You often want more columns of the grouped table — the department’s name and its location. Listing all of them in GROUP BY works, but there’s a shorter way: group by the primary key. PostgreSQL knows that every other column of that table has exactly one value per key, so it allows them:

SELECT d.id, d.name, d.location, count(e.id) AS employees, round(avg(e.salary), 2) AS avg_salary
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id
ORDER BY d.id;

(LEFT JOIN and count(e.id) keep departments with no employees, counted as 0.)

This works only for the primary key of a table in the query — not for any unique-looking column like d.name.

4. One row per group: DISTINCT ON

“The highest-paid employee in each department” isn’t an aggregate. You want a whole row, chosen by an order. PostgreSQL’s DISTINCT ON keeps the first row of each group, in the order you give:

SELECT DISTINCT ON (d.id) d.name AS department, e.first_name, e.last_name, e.salary
FROM departments d
JOIN employees e ON e.department_id = d.id
ORDER BY d.id, e.salary DESC;

The same with a window function, which also works in other databases:

SELECT department, first_name, last_name, salary
FROM (
  SELECT d.name AS department, e.first_name, e.last_name, e.salary,
         row_number() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS rank
  FROM departments d
  JOIN employees e ON e.department_id = d.id
) ranked
WHERE rank = 1
ORDER BY department;