MySQL GROUP BY Statement

The GROUP BY statement is used to group the records of the table(s) based on one or more column.

Its also possible to perform aggregate functions like COUNT, SUM, MIN, MAX, AVG etc. on the grouped column.

Syntax for GROUP BY Statement

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s);

Consider the table employees with the following data.

empno name age location salary
1 Andrew 30 India 100000
2 Beslin 29 India 90000
3 Joanna 23 USA 500000
4 Tianna 22 Canada 500000

Example query for GROUP BY

SELECT location,COUNT(empno) as count
FROM employees  
GROUP BY location;

Output

location count
Canada 1
India 2
USA 1

Example 2

SELECT location,COUNT(empno) as count, SUM(salary) as total_pay
FROM employees  
GROUP BY location;

This query will get the following output.

location count total_pay
Canada 1 500000
India 2 190000
USA 1 500000

The above query will retrieve the data of employee count and total salary in that location.

Example 3

SELECT location,COUNT(empno) as count, MAX(salary) as max_salary
FROM employees  
GROUP BY location;

The above query will retrieve the employee count in the locations and the maximum salary in that location.
Output

location count max_salary
Canada 1 500000
India 2 100000
USA 1 500000

Example 4

SELECT location,COUNT(empno) as count, AVG(salary) as avg_salary
FROM employees  
GROUP BY location;

The above query will retrieve the employee count in the locations and the average salary in that location.

Output

location count avg_salary
Canada 1 500000
India 2 95000
USA 1 500000

Most Read