Group By Clause in SQL

In SQL Server GROUP BY clause is used with the SELECT statement. It is used to group similar data into one group based on specified conditions. It groups result into one or more columns. It generally used with aggregate functions.

Syntax of Group by Clause:

Select COLUMN_NAME1, COLUMN_NAME2 From [Table | VIEW ]

Where Condition

Group By COLUMN_NAME1, COLUMN_NAME2

Order by COLUMN_NAME1, COLUMN_NAME2

Example:

CREATE TABLE MST_EMPLOYEE_MASTER

(

      EMP_ID NUMERIC(18,0)IDENTITY(1,1),

      EMP_NAME VARCHAR(50),

      DEPARTMENT_NAME VARCHAR(25),

      SALARY NUMERIC(18,2)

);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Shashi','Software Development',50000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Vikas','Software Development',100000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Prince','Software Testing',80000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Mathew','Software Testing',50000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Rahul','Software Testing',50000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Jonson','Human Resource',60000.00);

1.Select sum of salary department wise in an organization.

Select DEPARTMENT_NAME,Sum(SALARY) As TotalSalary From MST_EMPLOYEE_MASTER

Group By DEPARTMENT_NAME;

2.Select Highest Salary department wise.

Select DEPARTMENT_NAME,Max(SALARY) As MaxSalary From MST_EMPLOYEE_MASTER

Group By DEPARTMENT_NAME ORDER BY DEPARTMENT_NAME;