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.