MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL References

MySQL - GROUP BY Keyword



The MySQL GROUP BY keyword is used to arrange result table into identical groups with the help of aggregate functions (COUNT, MAX, MIN, SUM, AVG etc). The GROUP BY keyword follows the WHERE clause in a SELECT statement and precedes the ORDER BY keyword.

Syntax

The syntax for using GROUP BY Keyword in MySQL is given below:

SELECT column1, column2
FROM table_name
WHERE condition(s)
GROUP BY column1, column2
ORDER BY column1, column2;

Example:

Consider a database table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshLondon283000
6HuangLondon282800

  • GROUP BY single column: To find the number of employee present in the Employee table in different age group sorted by Age (descending order), the query is:

    SELECT Age, Count(Name) AS Number_of_Employee
    FROM Employee
    GROUP BY Age
    ORDER BY Age DESC;
    

    This will produce the result as shown below:

    AgeNumber_of_Employee
    301
    282
    271
    251
    241
  • GROUP BY multiple columns: To find the average salary of employees present in the Employee table ordered by City and Age, the query is:

    SELECT City, Age, AVG(Salary) AS AverageSalary
    FROM Employee
    GROUP BY City, Age
    ORDER BY City, Age;
    

    This will produce the result as shown below:

    CityAgeAverageSalary
    Amsterdam303100.0
    London253000.0
    London282900.0
    New York242750.0
    Paris272800.0