Group By Clause

Group by clause is used with the select statement to arrange identical data into groups. The Group by clause returns one row per group.

The GROUP BY clause is often used with aggregate functions such as AVG(), COUNT(), MIN(), MAX() and SUM(). In this case, the aggregate function returns the summary information per group.

Syntax:

SELECT Column1, Column2, Aggregate_function(expression1)

  FROM Table_Name

[WHERE Clause]

GROUP BY Column1, Column2;

Note: Aggregate_functions are MIN, MAX, SUM, Count, AVG etc.

Example:

Table Name “EMPLOYEE”

ID	NAME	ADDRESS	DEPARTMENT	SALARY
101	Rajesh	Ghaziabad	IT	10000
102	Swati	Delhi	Non IT	8000
103	Manoj	Noida	IT	15000
104	Sachin	Noida	IT	12000
105	Swati	Delhi	Non IT	8000

·         Write a query to find Department wise total salary and number of employee

SELECT DEPARTMENT,

            SUM(SALARY) as TOTAL_SALARY,

            COUNT(ID) as NO_OF_EMP

  FROM EMPLOYEE

GROUP BY DEPARTMENT;

Output of the above query will be:

DEPARTMENT	TOTAL_SALARY	NO_OF_EMP
IT	37000	3
Non IT	16000	2

·         Write a query to find how many employees in an address.

SELECT ADDRESS, COUNT(ID) as NO_OF_EMP

  FROM EMPLOYEE

GROUP BY ADDRESS;

Output of the above query will be:

ADDRESS	NO_OF_EMP
Ghaziabad	1
Delhi	2
Noida	2

·         Write a query to find out number of employee with the same name.

SELECT NAME, COUNT(ID)

  FROM EMPLOYEE

GROUP BY NAME;

Output of the above query will be:

Name	NO_OF_EMP
Rajesh	1
Swati	2
Manoj	1
Sachin	1