Writing SQL query for getting maximum occurrence of a value in a column

I have an emp ta开发者_StackOverflowble with the records below:

INSERT into emp(EmpId,Emp name, Manager)
Values(1,A,M1)
values(2,B,M1)
values(3,C,M2)
values(4,D,M3)

How can I find the Manager having the maximum number of employees under him? In this case, output should be M1. Please help.

select manager, count(*) as employees from emp
  group by manager
  order by count(*) desc

Take the first record. Depending on your SQL version, you can do this with a limit statement.

In SQL Server...

SELECT TOP 1 Manager
FROM  ( SELECT Manager,
               COUNT(Manager) as "ManagerCount"
        FROM emp
        GROUP BY Manager
        ORDER BY "ManagerCount" DESC )

Oracle is a bit different...

SELECT Manager
FROM  ( SELECT Manager,
               COUNT(Manager) as "ManagerCount"
        FROM emp
        GROUP BY Manager
        ORDER BY "ManagerCount" DESC )
WHERE ROWNUM <= 1

SELECT
    Manager,
    count(Manager) AS 'Num of Emps'
FROM
    emp
GROUP BY
    Manager
ORDER BY
    'Num of Emps' DESC

The first record will be the manager with the most employees. Also, based on the db provider, you can limit the result set to 1, so you only get the highest record. Here's an example using sql server:

  SELECT
    TOP 1 Manager,
    count(Manager) AS 'Num of Emps'
FROM
    emp
GROUP BY
    Manager
ORDER BY
    'Num of Emps' DESC

In Postgresql, create schema Test:

create table Test.Employee (Emp_id numeric, manager_id numeric, Manager_name varchar(20));

insert into Test.Employee(emp_id, manager_id, manager_name ) values(1, 3, 'A'),  (2, 3, 'A'), (3, 3, 'A'), (4, 3, 'A'), (5, 11, 'B'), (6, 12, 'C'), (7, 11, 'B');

select manager_name from (select count(manager_id) as mcount, manager_name from test.employee group by manager_name order by mcount DESC) AS TEMP limit 1

If you want the row from the emp table, use this:

select * from emp
where empid in (select manager from 
    (select manager, count(*)
     from emp
     group by 1
     having count(*) = (select max(count) from (select manager, count(*) as count from emp group by 1) x)
    ) y );

This will also return multiple rows in case there is a tie for the most number of employees.

SELECT
    count(e.last_name) count,
    d.last_name
FROM
    employees e
LEFT OUTER JOIN employees d ON e.manager_id = d.employee_id
GROUP BY
    d.last_name
ORDER BY
    count DESC;

Tested With SQL Server 2017

Select TOP 1 City, Count(City) AS 'MAX_COUNT' FROM Customer Group By City Order By 'MAX_COUNT' DESC;

Hope this simple query will help many one.

If you are using Oracle Database, you can simply use stats_mode function this will return single value with highest occurrences.

select stats_mode(manager) from emp;

This is very easy to use function instead of writing multiple lines of sql query.