MariaDB Group By

Summary: in this tutorial, you will learn how to use the MariaDB group by clause to group data.

Introduction to MariaDB group by clause

The group by clause groups rows of a result into groups. The following illustrates the syntax of the group by clause:

select
    select_list
from
    table_name
group by
    column1, column2,...;
Code language: SQL (Structured Query Language) (sql)

The group by is often used with aggregate functions including count(), min(), max(), sum(), and avg() to find properties of groups such as the number of elements (count), the total of values (sum), the maximum element (max), the minimum element (min), and the average of elements (avg).

The following shows the syntax of the group by clause used with an aggregate function:

select
    column1,
    aggregate_function(column2)
from
    table_name
group by
    column1;
Code language: SQL (Structured Query Language) (sql)

In this syntax, the group by clause groups rows into groups and the aggregate function is applied to each group to return the summary row.

MariaDB group by clause examples

We’ll use the countries and regions tables from the nation sample database for the demonstration.

A) Using the MariaDB group by clause with the count() function example

The following statement uses the group by clause with the count() function to get the number of countries in each region:

select 
    region_id, 
    count(country_id) 
from 
    countries
group by 
    region_id
order by 
    region_id;
Code language: SQL (Structured Query Language) (sql)

In this example:

• First, the group by clause divides the countries by regions.
• Then, the count() function is applied to each region to return the number of countries.

To make the output more meaningful, you can join the countries table with the regions table:

select 
    regions.name, 
    count(country_id) country_count
from 
    countries
inner join regions using (region_id)
group by 
    regions.name
order by 
    regions.name;
Code language: SQL (Structured Query Language) (sql)

B) Using the MariaDB group by clause with the sum() function

The following example uses the group by clause with the sum() function to calculate the total area of countries in each region:

select 
    regions.name region, 
    sum(area) region_area
from 
    countries
inner join regions 
    using (region_id)
group by
    regions.name
order by 
    region_area desc;
Code language: SQL (Structured Query Language) (sql)

C) Using the MariaDB group by clause with themin() and max() functions

The following example uses the group by clause with the min() and max() functions to find the minimum and maximum areas of countries in each region:

select 
    regions.name region, 
    min(area) smallest_country_area,
    max(area) largest_country_area
from countries
inner join regions using (region_id)
group by 
    regions.name
order by 
    regions.name;
Code language: SQL (Structured Query Language) (sql)

D) Using the MariaDB group by clause with the avg() function

The following example uses the group by clause with the avg() function to calculate the average area of countries in each region:

select 
    regions.name region, 
    avg(area) avg_area
from 
    countries
inner join regions 
    using (region_id)
group by 
    regions.name
order by 
    avg_area desc;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the MariaDB group by clause to group data.