In a MySql query how to return the names of the people who are reported to-CodePudding

I had this question during a test of a company, in MySql: How to return the names of the people who are reported to (excluding nulls) the number of members that report to them and the average age of those members as an integer. The rows should be ordered by the names in alphabetical order.

The table: ID, FirstName, LastName, ReportsTo, Position, Age

When I put COUNT(ReportsTo) the results shrink

I had this query:

SELECT ReportsTo, Age
FROM maintable_A9x
WHERE
ReportsTo IS NOT NULL
ORDER BY ReportsTo DESC

The solution comes with an INNER JOIN?

I tried:

SELECT ReportsTo, COUNT(ReportsTo), Age
FROM maintable_A9x
WHERE
ReportsTo IS NOT NULL
GROUPBY ReportsTo
ORDER BY ReportsTo DESC

But still, with COUNT(ReportsTo) the results shrink and only one line of results come

CodePudding user response：

You can try something like this:

SELECT
  CONCAT(M.FirstName," ",M.LastName) AS Member,
  COUNT(R.ID) AS ReportedTo,
  ROUND(AVG(R.Age)) AS AvgAge
FROM members AS M
LEFT JOIN members AS R ON M.ID = R.ReportsTo
WHERE M.ReportsTo IS NULL
GROUP BY M.ID
ORDER BY Member