IntermediateJOINs (INNER, LEFT, RIGHT, FULL)

Get employee count and avg salary per location

The query

SQL

SELECT
  d.location,
  COUNT(e.emp_id) AS employee_count,
  ROUND(AVG(e.salary), 0) AS avg_salary,
  STRING_AGG(DISTINCT d.dept_name, ', ') AS departments
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.location
ORDER BY employee_count DESC;

Tested against PostgreSQL 16

Note

“Aggregating across joined tables. STRING_AGG collects all department names per location.

Tables referenced

employees10 cols
departments5 cols