How to calculate Span of Control

Formula

Span of control = direct reports ÷ managers (or direct reports per manager)

SQL query

This example assumes: employees(id, name text, manager_id bigint, end_date date). It is written in PostgreSQL syntax; date functions differ slightly in MySQL, BigQuery and SQLite, and your column names will differ from these.

SELECT m.id AS manager_id,
       m.name AS manager,
       COUNT(e.id) AS direct_reports
FROM employees m
JOIN employees e ON e.manager_id = m.id AND e.end_date IS NULL
WHERE m.end_date IS NULL
GROUP BY m.id, m.name
ORDER BY direct_reports DESC;

Common mistakes that make Span of Control wrong

• Only count current employees; leavers still pointing to a manager inflate the figure.
• Dotted-line or matrix reporting is usually absent from a single manager_id column.
• An average span hides managers with one report and managers with twenty.

Ask for Span of Control in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. It does not replace your judgement about the definition: check that the query matches the formula your business uses.

• How many direct reports does each manager have?
• Which managers have more than 10 direct reports?