How to retrieve all the Department Names and the total number of Employees with in each department in SQL

SQL Script to create the required tables

Create Table Departments

(

     DepartmentID int primary key,

     DepartmentName nvarchar(50)

)

GO

Create Table Employees

(

     EmployeeID int primary key,

     EmployeeName nvarchar(50),

     DepartmentID int foreign key references Departments(DepartmentID)

)

GO

Insert into Departments values (1, 'IT')

Insert into Departments values (2, 'HR')

Insert into Departments values (3, 'Payroll')

Insert into Departments values (4, 'Admin')

GO

Insert into Employees values (1, 'Mark', 1)

Insert into Employees values (2, 'John', 1)

Insert into Employees values (3, 'Mike', 1)

Insert into Employees values (4, 'Mary', 2)

Insert into Employees values (5, 'Stacy', 2)

GO

SQL Query with Right Join

Select DepartmentName, Count(Employees.DepartmentID) as TotalEmployees

From Employees

Right Join Departments

ON Departments.DepartmentID = Employees.DepartmentID

Group By DepartmentName

Order By TotalEmployees