Displaying Department Name Having Highest Average Salary 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 name of the department having the highest average salary of employees working in it. We shall use the TOP, AVG, ORDER BY, AS, GROUP BY, and DESC clauses 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 having the highest average salary obtained by the employees working in that department. We will use the aggregate function AVG 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 calculates the average. Then these averages are sorted in descending order using ORDER BY and DESC clauses. Then the topmost row is selected using the TOP clause followed 1. 1 here indicates that the query returns only 1 row. We also name the new column of the department-wise salary as MAX_AVG_SALARY using the AS clause which creates kind of an alias.

Syntax:

SELECT TOP 1 COLUMN1, AVG(COLUMN2) AS ALIAS
FROM TABLE_NAME
GROUP BY COLUMN1
ORDER BY AVG(COLUMN2) DESC;

Query:

SELECT TOP 1 DEPARTMENT_NAME, AVG(SALARY) AS MAX_AVG_SALARY
FROM COMPANY
GROUP BY DEPARTMENT_NAME
ORDER BY AVG(SALARY) DESC;

Note: This query returns only 1 row i.e. the topmost row among the returned sorted rows.

Output:

Method 2: Using a Common Table Expression (CTE) and ROW_NUMBER()

Query:

WITH AverageSalaries AS (
    SELECT DEPTNAME, AVG(salary) AS AvgSalary
    FROM DEPARTMENT
    GROUP BY DEPTNAME
)
SELECT DEPTNAME,AvgSalary
FROM (
    SELECT DEPTNAME, AvgSalary, ROW_NUMBER() OVER (ORDER BY AvgSalary DESC) AS Rank
    FROM AverageSalaries
) AS RankedSalaries
WHERE Rank = 1;

Output:

sql output