Find Maximum and Minimum salary by Department in SQL Server

Let’s first create a table with four fields.

CREATE TABLE [dbo].[table1](
    [id] [int] NULL,
    [Name] [varchar](30) NULL,
    [Salary] [int] NULL,
    [DepId] [int] NULL
)

Now insert some records into this table.

INSERT table1 VALUES (1, 'test1', 1200, 1)
INSERT table1 VALUES (2, 'test2', 1500, 1)
INSERT table1 VALUES (3, 'test3', 1300, 2)
INSERT table1 VALUES (4, 'test4', 2000, 3)
INSERT table1 VALUES (5, 'test5', 1000, 2)
INSERT table1 VALUES (6, 'test6', 1300, 2)

If you are using SQL Server 2008 r2 or above than you can use the following code.

INSERT table1 VALUES (1, 'test1', 1200, 1), (2, 'test2', 1500, 1),(3, 'test3', 1300, 2),(4, 'test4', 2000, 3),(5, 'test5', 1000, 2),(6, 'test6', 1300, 2)

Select all records of the table.

select * from table1

Output



Now write a query for the min and max salary by department:

 

select depid, max(salary) as MaxSalary, min(Salary) as MinSalary from table1 group by DepId

Output



I hope you enjoy this article.

Happy coding.