Reputation: 43

select department(s) with maximum number of employees

I have two tables EMP(id,name,DEPT_id) and DEPT(id ,name). I need to find the department(s) in which the maximum number of employees work. Please help.

Upvotes: 4

Answers (12)

Deepak Bishoyi

Reputation: 1

select deptno,count(*)from emp group by
deptno having count(*)=(select max(count(*))from emp group by deptno);

Upvotes: 0

srini

Reputation: 1

If you have only emp table then below query will hep you get a results -

select a.* from (select deptno, dense_rank() over(order by count(*) desc ) as rank  from dbo.emp group by deptno) a  where a.rank =1

Upvotes: 0

Aditya Mishra

Reputation: 23

select Top 1 d.DNAME,count(e.ename) as counts from emp e,dept d where d.DEPTNO=e.DEPTNO
group by d.DNAME
order by counts desc

select d.DNAME,count(e.ename) as counts from emp e,dept d where d.DEPTNO=e.DEPTNO
group by d.DNAME
having count(e.ename) = (select max(micount) from (select count(deptno) micount from emp group by DEPTNO) a)

Upvotes: 1

Omid Ziyaee

Reputation: 428

you can solve this using with statement like this:

with deps as
 (select dep.department_name as dep_name, count(emp.employee_id) as cnt
    from departments dep
   inner join employees emp
      on emp.department_id = dep.department_id
   group by dep.department_name)
select deps.dep_name,cnt from deps 
where cnt=(select max(cnt) from deps)

select dep.department_name as dep_name, count(emp.employee_id) as cnt
  from departments dep
 inner join employees emp
    on emp.department_id = dep.department_id
 group by dep.department_name
having count(emp.employee_id) >= all (select count(emp.employee_id) as cnt
                                        from departments dep
                                       inner join employees emp
                                          on emp.department_id =
                                             dep.department_id
                                       group by dep.department_name)

with s1 as
 (select dep.department_name as dep_name,
         count(emp.employee_id) over(partition by dep.department_name) as cnt
    from departments dep
   inner join employees emp
      on emp.department_id = dep.department_id
   order by cnt desc),
s2 as
 (select s1.dep_name,
         s1.cnt,
         row_number() over(order by cnt desc) as row_num
    from s1)
select dep_name from s2 where row_num = 1

these solutions are proper for databases like Oracle that we do not have top(1) or limit 1

Upvotes: 1

mk2683

Reputation: 627

This will give the department name of the department which is having maximum number of employees.

Select DEPT_NAME from department where DEPT_ID = (select DEPT_ID from (Select DEPT_ID, count(DEPT_ID) from Employee group by DEPT_ID order by count(DEPT_ID) desc) where rownum = 1);

Upvotes: 2

Yamini RV

Reputation: 1

SELECT department_id, count(employee_id) as 'No_of_Emp'
    FROM employees
    GROUP BY department_id
    ORDER BY No_of_Emp DESC

Upvotes: -2

Neeraj vishwakarma

Reputation: 117

This question can be solved in multiple ways

Using sub query

SELECT name FROM dept WHERE id IN (SELECT dept_id FROM emp HAVING COUNT(dept_id) IN (SELECT MAX(COUNT(dept_id)) FROM emp) GROUP BY dept_id)

Using Join

SELECT name FROM emp e INNER JOIN dept d ON e. dept_id = d. id HAVING COUNT(e.dept_id) IN (SELECT MAX(COUNT(dept_id)) from emp) group by dept_id)

Upvotes: 0

sayaksan

Reputation: 16

Now, EMP(id,name,DEPT_id) and DEPT(id ,name) these two tables are given. Now, I insert some entries in the table in such a manner that:

SELECT COUNT(*) AS NO_OF_EMPLOYEES,
       DEPARTMENT.DEPT_NAME
  FROM EMP, DEPARTMENT
 WHERE EMP.DEPT_ID=DEPARTMENT.DEPT_ID
 GROUP BY EMP.DEPT_ID
 ORDER BY NO_OF_EMPLOYEES;

This query generates the following:

NO_OF_EMPLOYEES DEPT_NAME
3               Research
3               Finance
4               Sales
4               Product

Now, the query which gives the correct result:

SELECT COUNT(*) AS MAX_NO_OF_EMPLOYEES,
       DEPARTMENT.DEPT_NAME
  FROM EMP, DEPARTMENT
 WHERE EMP.DEPT_ID=DEPARTMENT.DEPT_ID
 GROUP BY EMP.DEPT_ID
HAVING MAX_NO_OF_EMPLOYEES=(
    SELECT COUNT(*) AS NO_OF_EMPLOYEES
      FROM EMP
     GROUP BY DEPT_ID
     ORDER BY NO_OF_EMPLOYEES DESC
     LIMIT 1
);

It will generate:

MAX_NO_OF_EMPLOYEES    DEPT_NAME
4                      Sales
4                      Product

Upvotes: 0

Abhishek Sahay

Reputation: 141

you can create view to find it.

CREATE VIEW TEMP AS SELECT COUNT(EMP.id) AS A, DEPT.name AS B 
FROM EMP JOIN DEPT ON EMP.DEPT_id=DEPT.id GROUP BY DEPT.id;

SELECT MAX(A) FROM TEMP;

Upvotes: 0

zedfoxus

Reputation: 37039

Just a little more verbose than the other two solutions, but it will get the job done...feel free to tweak to your convenience.

select countbydept.*
from
(
  -- from EMP table, let's count number of records per dept
  -- and then sort it by count (highest to lowest)
  -- and take just the first value. We just care about the highest
  -- count
  select dept_id, count(*) as counter
  from emp
  group by dept_id
  order by counter desc
  limit 1
) as maxcount

inner join

(
  -- let's repeat the exercise, but this time let's join
  -- EMP and DEPT tables to get a full list of dept and 
  -- employe count
  select
    dept.id,
    dept.`name`,
    count(*) as numberofemployees
  from dept
  inner join emp on emp.dept_id = dept.id
  group by dept.id, dept.`name`
) countbydept 

-- combine the two queries's results by matching the employee count
on countbydept.numberofemployees = maxcount.counter

Example: http://sqlfiddle.com/#!9/7d6a2d/1

Upvotes: 1

Mahedi Sabuj

Reputation: 2944

You can try this query.

Select Id, Name from Dept
Where Id = (Select Top(1) DeptId from Emp 
            Group By DeptId
            order by Count(DeptId) desc)

Upvotes: 0

KTAnj

Reputation: 1356

Try this query.

SELECT a.name,Max(a.NumEmp) AS maxEmpCount FROM ( SELECT d.name,COUNT(*) AS NumEmp FROM EMP e INNER JOIN DEPT d  ON e.DEPT_id = d.id GROUP BY e.DEPT_id ) AS a GROUP BY a.name

Upvotes: 1

select department(s) with maximum number of employees

Answers (12)

Related Questions