How would I return all the information from the row?-CodePudding

I have a table with managers and the employees they manage as well as fName, lName etc. I need to count the number of employees a manager manages and return the maximum as well as all the rest of the information in the row.

SELECT TOP 1 Manager, COUNT (Manager) AS Manager_Count FROM EMPLOYEE
GROUP BY Manager ORDER BY COUNT(*) DESC

This is what I have but only returns the manager and the count. I have tried adding nested SELECT as well as SELECT * but just keep getting errors. Thanks for any help.

CodePudding user response：

If you are talking about wanting all information for a given manager and the count of employees that manager has, I would suspect the manager is an employee, but also the manager is also a column per employees they are responsible for, such as:

EmployeeId	Name	Manager
1	Mary	0
2	Joe	1
3	Jane	1
4	Bob	2

Where Mary is the top-level manager and has two direct employees, but Joe is also a manager with one person under him. So your base query is correct

select top 1
      e.Manager,
      count(*) NumberOfEmployees
   from
      Employee e
   group by 
      e.Manager
   order by
      count(*) desc

So this gives you the manager's employee ID and the count. Now, from this aggregated result, join back to the employee table for the manager's direct details.

select
      e2.*,
      Mgr.NumberOfEmployees
   from
      ( select top 1
              e.Manager,
              count(*) NumberOfEmployees
           from
              Employee e
           group by 
              e.Manager
           order by
              count(*) desc ) Mgr
         Join Employee e2
            on Mgr.Manager = e2.EmployeeId