Part 8 SQL Query to find department with highest number of employees

Link for all dot net and sql server video tutorial playlists

Link for slides, code samples and text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Scenario asked in the SQL Server Interview
Based on the above two tables write a SQL Query to get the name of the Department that has got the maximum number of Employees. To answer this question it will be helpful if you the knowledge of JOINS & GROUP BY in SQL Server. We discusses these in Parts 11 & 12 of SQL Server Tutorial video series.

SQL query that retrieves the department name with maximum number of employees
SELECT TOP 1 DepartmentName
FROM Employees
JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
GROUP BY DepartmentName
ORDER BY COUNT(*) DESC

Scenario asked in the SQL Server Interview
Based on the above two tables write a SQL Query to get the name of the Department that has got the maximum number of Employees. To answer this question it will be helpful if you the knowledge of JOINS & GROUP BY in SQL Server. We discusses these in Parts 11 & 12 of SQL Server Tutorial video series.

SQL query that retrieves the department name with maximum number of employees
SELECT TOP 1 DepartmentName
FROM Employees
JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
GROUP BY DepartmentName
ORDER BY COUNT(*) DESC

[sanrnsam]

Venkat Sir. You are GODLY! So thankful to you! :)

[nextcertification]

Thanks for all your tutorials, in this particular script, better to get the count of employees in the employee table, then use join or where. It has better performance.
cheers

[barrivikram]

create table dep
( dep_id int,
dep_name varchar(20)
)
insert into dep values (1, 'it')
insert into dep values (2, 'hr')
insert into dep values (3, 'payroll')

select * from dep
drop table dep
create table emp
( empid int,
empname varchar(20),
dep_id int,
)
select * from emp


insert into emp values(1, 'raju', 1)
insert into emp values(2, 'ravi', 1)
insert into emp values(3, 'ram', 2)
insert into emp values(4, 'ravan', 1)
insert into emp values(5, 'raghu', 3)
insert into emp values(6, 'ravindra', 2)

select * from dep
select * from emp

select top 1 dep_name, count(*) as employeecount
from emp
join dep on emp.dep_id = dep.dep_id
group by dep_name
order by employeecount desc

select top 1 dep_name
from emp
join dep on emp.dep_id = dep.dep_id
group by dep_name
order by count(*) desc

[TwoMinutesisEnough]

Learning after 10 years from this video creation,

[persistencej]

Your Tutorials are so helpful to the beginners ... Thanks a lot

[prabhakarpatil]

what if 2 department has same employee count?

[swetashaw]

Never realized that we can use aggregate functions in order by clause🤨

[helriperhr]

Gracias kudvenkat excelentes tutoriales. Saludos..

[ThisCanNotBTheFuture]

As someone mentioned, more than one department might have the most number of employees, in which case the following works even though it isn't exactly pretty:

SELECT DepartmentName
FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
GROUP BY DepartmentName
HAVING COUNT(*) =
(
SELECT MAX(t.num) FROM
(
SELECT COUNT(*) as 'num'
FROM Employees
JOIN Departments ON Employees4.DepartmentID = Departments.DepartmentID
GROUP BY DepartmentName
) t
)

[alexb]

What if you wanted to return all rows with at least a certain constant number of employees? So for your example let’s say we wanted to get all the departments with more than 1 employee, could we just add a HAVING clause after GROUP BY to specify EmployeeCount>1?

[jalvoice]

Very helpful, do you provide tutoring 1:1? please let me know

[basic_datastructure]

Student(st_roll, st_name, st_address)
Teacher(t_name, t_address, t_designation)
Class(cl_room_no, year, subject_id)
Subject(subject_id, t_name)
Marks(st_roll, subject_id, marks)

Find the name of student who got maximum total marks.


can u please ans this ques.. it's too urget

[gopinathm]

Hi sir, can you explain cte and temporary table which performance wise good.

[yonasm]

In case more than one Departments have highest Number of employees try the following option:-


SELECT TOP 1 WITH TIES DepartmentName
FROM Employees
JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
GROUP BY DepartmentName
ORDER BY COUNT(*) DESC

[seemasolanki]

Hi Venkat, Please could you Provide the Query for Department having Max salary.Thanks in Advance

[satyxy]

Hi Venkat, in the above scenario can we avoid group by and order by as its gonna return only department name?? . pls correct me if am wrong....

[shabashvalishaik]

sir small how to get 3rd heighest emp salary each department

[anjalishreya]

if suppose we want highest no of person in one departmment not by join we have all data in on table then

[manjuelaiya]

sir if i use top 1 ...i am getting error like from keyword not found where expected what should i do and this is the query i used select top 1 department_name
from Department
join Staff
on
group by department_name
order by count(*) desc;

[unvandy]

Dear sir
how to query to select each 5 row category in sql server?