Finding Average Salary of Each Department in SQL Server

In SQL, we need to find out the department-wise information from the given table containing information about employees. One such data is the average salary obtained by the employees in each department. We shall use the GROUP BY and AVG clause to achieve this. This is illustrated below. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table COMPANY inside the database GeeksForGeeks. This table has 4 columns namely EMPLOYEE_ID, EMPLOYEE_NAME, DEPARTMENT_NAME, and SALARY containing the id, name, department, and the salary of various employees.

Query:

CREATE TABLE COMPANY(
EMPLOYEE_ID INT PRIMARY KEY,
EMPLOYEE_NAME VARCHAR(10),
DEPARTMENT_NAME VARCHAR(10),
SALARY INT);

Output:

Step 4: Describe the structure of the table COMPANY.

Query:

EXEC SP_COLUMNS COMPANY;

Output:

Step 5: Insert 5 rows into the COMPANY table.

Query:

INSERT INTO COMPANY VALUES(1,'RAM','HR',10000);
INSERT INTO COMPANY VALUES(2,'AMRIT','MRKT',20000);
INSERT INTO COMPANY VALUES(3,'RAVI','HR',30000);
INSERT INTO COMPANY VALUES(4,'NITIN','MRKT',40000);
INSERT INTO COMPANY VALUES(5,'VARUN','IT',50000);

Output:

Step 6: Display all the rows of the COMPANY table.

Query:

SELECT * FROM COMPANY;

Output:

Step 7: Display the name of the department along with the average salary obtained by the employees working in that department. We will use the aggregate function i.e. the AVG clause here to calculate the average(mean) salary of each department. The department-wise average is obtained with the help of the GROUP BY clause which groups all the salaries of a specific department together and then calculated the average. We also name the new column of the department-wise salary as AVERAGE_SALARY using the AS clause which creates kind of an alias.

Syntax:

SELECT DEPARTMENT_NAME,AVG(SALARY) AS 
AVERAGE_SALARY FROM COMPANY GROUP BY DEPARTMENT_NAME;

Query:

SELECT DEPARTMENT_NAME,AVG(SALARY) AS 
AVERAGE_SALARY FROM COMPANY GROUP BY DEPARTMENT_NAME;

Note: This query returns only 3 rows because the table has 3 departments.

Output:

Method 2: Using a Common Table Expression (CTE)

Query: 

WITH DepartmentAverages AS (
    SELECT DEPTNAME, AVG(Salary) AS AverageSalary
    FROM DEPARTMENT
    GROUP BY DEPTNAME
)
SELECT DEPTNAME, AverageSalary
FROM DepartmentAverages;

Output: