MariaDB GROUP BY Keyword
The MariaDB 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 keyword in a SELECT statement and precedes the ORDER BY keyword.
Syntax
The syntax for using GROUP BY Keyword in MariaDB 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:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | London | 28 | 3000 |
6 | Huang | London | 28 | 2800 |
-
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:
Age Number_of_Employee 30 1 28 2 27 1 25 1 24 1 -
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:
City Age AverageSalary Amsterdam 30 3100.0 London 25 3000.0 London 28 2900.0 New York 24 2750.0 Paris 27 2800.0
❮ MariaDB Keywords