The Number of Employees Which Report to Each Employee
Task: Report the ids and the names of all managers, the number of employees who report directly to them, and the average age of the reports rounded to the nearest integer.
Table: Employees
+-------------+-------------+
| Column Name | Type |
+-------------+-------------+
| employee_id | INT |
| name | varchar(20) |
| reports_to | INT |
| age | INT |
+-------------+-------------+Input: Employees table:
+-------------+---------+------------+-----+
| employee_id | name | reports_to | age |
+-------------+---------+------------+-----+
| 9 | Hercy | null | 43 |
| 6 | Alice | 9 | 41 |
| 4 | Bob | 9 | 36 |
| 2 | Winston | null | 37 |
+-------------+---------+------------+-----+Output:
+-------------+-------+---------------+-------------+
| employee_id | name | reports_count | average_age |
+-------------+-------+---------------+-------------+
| 9 | Hercy | 2 | 39 |
+-------------+-------+---------------+-------------+View Reference Solution & Explanation ▼
This solution uses a `JOIN` to combine data from multiple tables, the `GROUP BY` clause to aggregate results, and the `ROUND()` function to format numerical output.
SELECT m.employee_id, m.name, COUNT(e.employee_id) AS reports_count, ROUND(AVG(e.age)) AS average_age FROM Employees m JOIN Employees e ON m.employee_id = e.reports_to GROUP BY m.employee_id, m.name ORDER BY m.employee_id;