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

By Posted on

Writing SQL query for getting maximum occurrence of a value in a column – This article will take you through the common SQL errors that you might encounter while working with sql, sql,  sql. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.

SQL Problem :

I have an emp table 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.

Solution :

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.

Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.