Count function shows multiple rows with value of 1 in MySQL-CodePudding

I'm refreshing my knowledge regarding SQL and came across this problem:

In this MySQL challenge, your query should return the names of the people who are reported to (excluding null values), 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

This is the main table:

ID  FirstName   LastName    ReportsTo       Position    Age
-----------------------------------------------------------
1   Daniel      Smith       Bob Boss        Engineer    25
2   Mike        White       Bob Boss        Contractor  22
3   Jenny       Richards    null            CEO         45
4   Robert      Black       Daniel Smith    Sales       22
5   Noah        Fritz       Jenny Richards  Assistant   30
6   David       S           Jenny Richards  Director    32
7   Ashley      Wells       David S         Assistant   25
8   Ashley      Johnson     null            Intern      25

The query should return this result set:

correct output

My query is this:

SELECT
    mt.ReportsTo, 
    COUNT(mt.ReportsTo) AS Members, 
    AVG(mt.Age) AS Average_Age
FROM 
    maintable_1YJ8B mt
WHERE 
    ReportsTo IS NOT NULL
GROUP BY
    mt.ReportsTo, mt.Age
ORDER BY 
    mt.ReportsTo

And my query returns this output:

ReportsTo        Members    Average_Age
---------------------------------------
Bob Boss         1          22.0000
Bob Boss         1          25.0000
Daniel Smith     1          22.0000
David S          1          25.0000
Jenny Richards   1          30.0000
Jenny Richards   1          32.0000

Question: what am I missing to make it return the correct result set? And how can I make the age column integers not decimal values?

Please help! Thank you!

CodePudding user response：

Your query is pretty close to the answer here. As somebody already suggested, don't group by age. That won't allow you to get a proper average across each manager's employees. I saw you other question was about rounding the number; this can be done with the "round" function, where we round to 0 decimal places. Here's my resulting query:

SELECT
  mt.ReportsTo, 
  COUNT(mt.ID) as Members, 
  ROUND(AVG(mt.Age), 0) as Average_Age_Rounded
FROM employee mt
  WHERE ReportsTo IS NOT NULL
  GROUP BY
    mt.ReportsTo
  ORDER BY mt.ReportsTo

And here is a db-fiddle with the answer: https://www.db-fiddle.com/f/eL36sEM4gj1KsM3pzHiAzR/0

CodePudding user response：

You should not grouping by 'Age' – try next query:

SELECT
  mt.ReportsTo,
  COUNT(mt.ReportsTo) as Members,
  AVG(mt.Age) as Average_Age
FROM MainTable mt
WHERE ReportsTo IS NOT NULL
GROUP BY mt.ReportsTo
ORDER BY mt.ReportsTo