MariaDB: GROUP BY Clause
This MariaDB tutorial explains how to use the MariaDB GROUP BY clause with syntax and examples.
Description
The MariaDB GROUP BY clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
Syntax
The syntax for the GROUP BY clause in MariaDB is:
SELECT expression1, expression2, ... expression_n, aggregate_function (expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n;
Parameters or Arguments
- expression1, expression2, ... expression_n
- The expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.
- aggregate_function
- It can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.
- tables
- The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
- WHERE conditions
- Optional. The conditions that must be met for the records to be selected.
Example - Using SUM function
Let's look at how to use the GROUP BY clause with the SUM function in MariaDB.
For example:
SELECT site_id, SUM(file_size) AS "Total" FROM pages WHERE site_id < 100 GROUP BY site_id;
This MariaDB GROUP BY example uses the SUM function to return the site_id and the total of all file_size values (for the site_id) where the site_id is less than 100.
Because you have listed one column (the site_id field) in your SELECT statement that is not encapsulated in the SUM function, you must use the GROUP BY clause. The site_id field must, therefore, be listed in the GROUP BY clause.
Example - Using COUNT function
Let's look at an example of how to use the GROUP BY clause with the COUNT function in MariaDB.
For example:
SELECT site_id, COUNT(*) AS "Number of pages per site" FROM pages WHERE site_name in ('TechOnTheNet.com', 'BigActivities.com') GROUP BY site_id;
This GROUP BY example uses the COUNT function to return the site_id and the number of pages (for that site_id) where the site_name is either 'TechOnTheNet.com' or 'CheckYourMath.com'.
Example - Using MIN function
Let's next look at how to use the GROUP BY clause with the MIN function in MariaDB.
For example:
SELECT site_id, MIN(file_size) AS "Smallest File" FROM pages where site_id < 50 GROUP BY site_id;
This GROUP BY example uses the MIN function to return the site_id and the minimum file_size for each site_id that is less than 50.
Example - Using MAX function
Finally, let's look at how to use the GROUP BY clause with the MAX function in MariaDB.
For example:
SELECT site_id, MAX(file_size) AS "Largest File" FROM pages where site_id < 50 GROUP BY site_id;
This GROUP BY example uses the MAX function to return the site_id and the maximum file_size for each site_id that is less than 50.
Advertisements