Maximum and minimum salary department wise in Sql server

Let’s first create a table having four fields.

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

Now insert some record in 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 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

1

Now write a query for min and max salary department wise

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

Output

2

I hope you enjoy this article.

Happy coding :)

Tags: ,