CODEWITHSUNDEEP
sql
Sanjit Kung
Sanjit Kung

Reputation: 11

Finding department having maximum number of employee

I have a table employee

id name  dept
1  bucky shp
2  name  shp
3  other mrk

How can i get the name of the department(s) having maximum number of employees ? ..

I need result 

dept
--------
shp

Upvotes: 0

Views: 5058

Answers (4)

Gaurav Khurana
Gaurav Khurana

Reputation: 3901

Based on one of the answer, Let me try to explain step by step

First of all we need to get the employee count department wise. So the firstly innermost query will run 

select count(*) cnt, deptno from scott.emp group by deptno

This will give result as

enter image description here

Now out of this we have to get the one which is having max. employee i.e. department 30.

Also please note there are chances that 2 departments have same number of employees

The second level of query is 

select rank() over (order by cnt desc) as rnk,cnt,deptno from
(
   select count(*) cnt, deptno from scott.emp group by deptno
)

Now we have assigned ranking to each department

enter image description here

Now to select rank 1 out of it. we have a simplest outer query

select * from 
(
  select rank() over (order by cnt desc) as rnk,cnt,deptno from
   (
      select count(*) cnt, deptno from scott.emp group by deptno
   )
)
 where rnk=1

So we have the final result where we got the department which has the maximum employees. If we want the minimum one we have to include the department table as there are chances there is a department which has no employees which will not get listed in this table

You can ignore the scott in scott.emp as that is the table owner.

The above SQL can be practised at Practise SQL online

Upvotes: 0

yahoo
yahoo

Reputation: 331

SELECT cnt,deptno FROM (
SELECT rank() OVER (ORDER BY cnt desc) AS rnk,cnt,deptno from
(SELECT COUNT(*) cnt, DEPTNO FROM EMP
GROUP BY deptno))
WHERE rnk = 1;

Upvotes: 1

gknicker
gknicker

Reputation: 5569

With common table expressions, count the number of rows per department, then find the biggest count, then use that to select the biggest department.

WITH depts(dept, size) AS (
  SELECT dept, COUNT(*) FROM employee GROUP BY dept
), biggest(size) AS (
  SELECT MAX(size) FROM depts
)
SELECT dept FROM depts, biggest WHERE depts.size = biggest.size

Upvotes: 0

sqluser
sqluser

Reputation: 5672

Assuming you are using SQL Server and each record representing an employee. So you can use window function to get the result

WITH C AS (
    SELECT  RANK() OVER (ORDER BY dept) Rnk
            ,name
            ,dept
    FROM table
)
SELECT TOP 1 dept FROM 
(SELECT COUNT(Rnk) cnt, dept FROM C GROUP BY dept) t
ORDER BY cnt DESC

Upvotes: 0

Related Questions