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.
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;
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.