IntermediateGROUP BY & Aggregation

STRING_AGG: list employee names per department

The query

SQL

SELECT
  d.dept_name,
  COUNT(e.emp_id) AS headcount,
  STRING_AGG(e.first_name || ' ' || e.last_name, ', ' ORDER BY e.last_name) AS employees
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_name;

Tested against PostgreSQL 16

Note

“STRING_AGG concatenates strings within a group with a separator. Very useful for reporting.

Tables referenced

employees10 cols
departments5 cols