IntermediateJOINs (INNER, LEFT, RIGHT, FULL)

Find departments with their total project budget

The query

SQL

SELECT
  d.dept_name,
  d.budget AS dept_budget,
  COUNT(p.project_id) AS project_count,
  COALESCE(SUM(p.budget), 0) AS total_project_budget
FROM departments d
LEFT JOIN projects p ON d.dept_id = p.dept_id
GROUP BY d.dept_id, d.dept_name, d.budget
ORDER BY total_project_budget DESC;

Tested against PostgreSQL 16

Note

“LEFT JOIN + COALESCE ensures departments with no projects still appear with 0 budget.

Tables referenced

departments5 cols
projects7 cols