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 |