-2

The title may be a little misguided but I am unsure of how to phrase it.

I am having an issue where I need to return the details of the manager with the most subordinates.

The table is currently set out like this for example.

Screenshot of database table

I am currently running this query to find the {manager} and how many times the {manager} has been assigned to employees:

SELECT *, count(manager) AS managercount FROM employee GROUP BY manager ORDER BY managercountb DESC LIMIT 1;

Result: manager: 101 managercount: 4

I however need to find a way to display all of the details of the manager via the employeeid.

I have attempted entering 'WHERE manager = employeeid' which will return nothing since no employee is the manager of themself.

I need advice on how to include all of the columns based on the manager with the most employees under them.

Joe
  • 41,484
  • 20
  • 104
  • 125
SLHEFC1928
  • 19
  • 1
  • 5
  • Do you know about joins? You can try to join the table with itself and think of something now..... – SR810 Jul 19 '20 at 04:45
  • The `DESC` actually is part of the `ORDER BY` statement, and has nothing to do with `LIMIT`. Are you sure you know the syntax? – Psi Jul 19 '20 at 05:03
  • I am not 100% confident with the syntax but my main point of concern if getting the details of the manager with the most employees. Thank you for letting me know though. – SLHEFC1928 Jul 19 '20 at 05:09

1 Answers1

-1

You need to implement self join i.e join the table with itself. Actually i don't have your table structure but i've tried to give you rough query structure. your query will be smiliar to one given below. Look at the structure and try to understand heirarchy.

    select Emp.employeeid, Emp.firstname, Emp.lastname, 
    Emp.job, Emp.manager, Emp.hired, Temp.mcount
    from Employee Emp inner join 
        (select employeeid, count(manager) as mcount from employee
        group by manager order by count(manager) desc limit 1) Temp 
   on Emp.employeeid = Temp.employeeid;
abhijeet chavan
  • 68
  • 1
  • 10
  • I hope this answer may be helpful to get your solution. If you think this post is helpful then please mark it as an answer by clicking there on correct tick. Once you click, it'll get green in color that represents you marked this post as answer. Thank you! – abhijeet chavan Jul 19 '20 at 05:31
  • Thank you for your reply, this is great but unfortunately, this will assign the largest value to all cells in the mcount column, I am looking to have a unique count, did you have any ideas? My current query with no limit will return the unique mcount values but it assigns the values to the wrong employee. – SLHEFC1928 Jul 19 '20 at 06:47
  • @SLHEFC1928 please check now – abhijeet chavan Jul 19 '20 at 07:02
  • Note that nobody ever uses RIGHT JOIN, and there's no Cartesian product here – Strawberry Jul 19 '20 at 07:30
  • Thanks @strawberry changed to inner join – abhijeet chavan Jul 19 '20 at 07:57
  • Yep, still no Cartesian product – Strawberry Jul 19 '20 at 07:59
  • @stawberry Actually before last to last edit i was using cartesian product. I mistakenly forgot to remove that line mentioning cartesian prroduct after editing my answer. Thanks for correcting me – abhijeet chavan Jul 19 '20 at 08:00
  • You weren't. And note that you'd typically GROUP BY any and all non-aggregated columns in the SELECT (as opposed to the *one* aggregated column). – Strawberry Jul 19 '20 at 08:01
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/218130/discussion-between-abhijeet-chavan-and-strawberry). – abhijeet chavan Jul 19 '20 at 08:01
  • @SLHEFC1928 Plz check now if this edited answer is helpful to you. If yes please mark it as an Answer – abhijeet chavan Jul 19 '20 at 08:11