GROUP BY Clause | Database

Introduction

GROUP BY clause in SQL is used to arrange identical data into groups. Groups are determined by the columns that you specify in GROUP BY clause. It returns one records for each group.

Syntax

GROUP BY clause syntax is

SELECT column_names
FROM table_name
WHERE condition
GROUP BY column-names

In this query, the GROUP BY clause produced a group for each combination of the values in the columns listed in the GROUP BY clause.

Example

Create a employee table (emp) and insert record into it.

/*Create Table */
CREATE TABLE emp(
  id INT,
  name VARCHAR(10),
  age INT,
  salary INT
)

/* Insert record */
INSERT INTO emp(id,name,age,salary)
VALUES(1, 'Rohan', 34, 5000),
(2, 'Scott', 43, 10000),
(3, 'Anu', 34, 8000),
(4, 'Vishal', 30, 5000),
(5, 'Shane', 25, 8000),
(6, 'Rohit', 40, 10000)

Multiple employee has same salary. Result set of query based on salary will have multiple row with same salary. To count the number of employee with same salary, GROUP BY clause along with COUNT clause is used.

/*Retrieve all record from Table */
SELECT name,age,salary FROM emp ORDER BY salary

/* Result of Query */
name    age salary
Rohan   34  5000
Vishal  30  5000
Anu     34  8000
Shane   25  8000
Scott   43  10000
Rohit   40  10000

/* Retrieve all record from Table and group by salary. */
SELECT name,age,salary FROM emp GROUP BY salary ORDER BY salary

/* Result of Query */
name  age salary
Rohan 34  5000
Anu   34  8000
Scott 43  10000


/* Retrieve all record from Table and count the number of employee who belong to same group */
SELECT age,salary,COUNT(salary) AS e_count FROM emp GROUP BY salary ORDER BY salary

/* Result of Query */
age salary  e_count
34  5000    2
34  8000    2
43  10000   2

GROUP BY clause is often used with aggregate functions for generating summary reports. An aggregate function performs a calculation on a group and returns a unique value per group. For example, COUNT() returns the number of rows in each group.

Reference

Group By Clause