Cheatsheet Pages / SQL Cheatsheet
How to join three tables and aggregate in SQL — orders per department
The answer spans three tables: order counts and revenue grouped by department.
Every example runs against the cheatsheet's sample tables — departments(id, name), employees(id, name, dept_id, salary, manager_id, hired_on) and orders(id, emp_id, amount, ordered_on) — and was executed and verified against SQLite, so you can paste it in and get the same result.
SELECT d.name AS dept, COUNT(o.id) AS order_count, SUM(o.amount) AS revenue
FROM departments d
JOIN employees e ON e.dept_id = d.id
JOIN orders o ON o.emp_id = e.id
GROUP BY d.id, d.name;
-- Engineering: 3 orders, 2370.50; Sales: 4 orders, 1650.74; HR has no orders and doesn't appear (INNER JOIN)