CareerCup

SELECT 
    Department.name, 
    COUNT(Employee.id)
FROM 
    Department
LEFT JOIN 
    Employee ON Department.dept_id = Employee.dept_id
GROUP BY 
    Department.dept_id, 
    Department.name
ORDER BY 
    COUNT(Employee.id) DESC, 
    Department.name

Looking for interview experience sharing and coaching?

Visit aonecode.com for private lessons by FB, Google and Uber engineers

Our ONE TO ONE class offers

SYSTEM DESIGN Courses (highly recommended for candidates for FLAG & U)
ALGORITHMS (conquer DP, Greedy, Graph, Advanced Algos & Clean Coding),
latest interview questions sorted by companies,
mock interviews.

Our students got hired from G, U, FB, Amazon, LinkedIn and other top tier companies after weeks of training.

Feel free to email us aonecoding@gmail.com with any questions. Thanks!

select 
	d.Name, 
	count(e.ID) as empCount
from 
	Department d
left outer join 
	Employee e on d.DEPT_ID = e.DEPT_ID
group by 
	d.Name
order by 
	empCount desc, 
	d.Name

select 
	d.Name, 
	count(e.ID) as empCount
from 
	Department d
left outer join 
	Employee e on d.DEPT_ID = e.DEPT_ID
group by 
	d.Name
order by 
	empCount desc, 
	d.Name

select d.name, count(e.id) over (partition by d.dept_id) as total_employees
from employee e join department d
on e.dept_id= d.dept_id
order by total_employees desc, d.name

{select d.name, count(e.id) over (partition by d.dept_id) as total_employees
from employee e join department d
on e.dept_id= d.dept_id
order by  total_employees desc, d.name

}

SELECT
Department.name,
COUNT(Employee.id)
FROM
Department
LEFT JOIN
Employee ON Department.dept_id = Employee.dept_id
GROUP BY
Department.dept_id,
Department.name
ORDER BY
COUNT(Employee.id) DESC,
Department.name

SELECT D.Name, A.empCnt
FROM DEPARTMENT AS D
CROSS APPLY (SELECT COUNT(*) AS empCnt
FROM EMPLOYEE AS E
WHERE E.DEPT_ID = D.DEPT_ID
) AS A
ORDER BY A.empCnt DESC, D.Name

SELECT 	D.Name,  A.empCnt
FROM 	DEPARTMENT AS D 
		CROSS APPLY (SELECT COUNT(*) AS empCnt
					 FROM  EMPLOYEE AS E
					 WHERE E.DEPT_ID = D.DEPT_ID
					) AS A
ORDER BY A.empCnt DESC, D.Name

SELECT D.department_id, D.department_name, Count(E.EMPLOYEE_ID)
FROM departments D
Left OUTER JOIN employees E
ON
E.department_id = D.department_id
Group by D.department_id, D.department_name
Having Count(E.EMPLOYEE_ID)<3
Order by Count(E.EMPLOYEE_ID) DESC, D.department_name;

select d.dname,count(empno)
from dept d,emp e
where d.DEPTNO=e.DEPTNO(+)
group by d.DEPTNO,d.dname
ORDER BY
COUNT(e.EMPNO) DESC,
d.dname;

select d.dname,count(empno)
from dept d,emp e
where d.DEPTNO=e.DEPTNO(+)
group by d.DEPTNO,d.dname
ORDER BY 
    COUNT(e.EMPNO) DESC, 
    d.dname

Select d.name as "Department Name",Count(e.id) as "No of Employees" from Department d
left outer join Employees e
on d.dept_id=e.dept_id
group by d.name
order by count(e.id) desc,d.name asc;

This is assuming that there is no department for unstaffed..

select coalease(d.name,'Unstaffed'),count(e.id) as No_of_Employees from Employee e
inner join Department d
on e.dept_id=d.dept_id
group by d.name
order by No_of_Employees desc,d.name asc;

Select temp.dept_name, temp.total from (Select d.dept_id, d.dept_name, count(emp_id) as total from dept_p d LEFT OUTER JOIN emp_p e on d.dept_id=e.dept_id group by dept_id,dept_name)temp order by temp.total desc, temp.dept_name;