MariaDB avg() Function

Summary: in this tutorial, you will learn how to use the MariaDB avg() function to calculate the average value of a set.

An overview of MariaDB avg() function

The MariaDB avg() is an aggregate function that accepts a set of numbers and returns the average.

The following illustrates the syntax of the avg() function:

avg(expression)
Code language: SQL (Structured Query Language) (sql)

The avg() function returns null if the set is empty. It ignores null values in the input arguments.

MariaDB avg() illustration

First, create a new table called avg_demos:

create table avg_demos(
    c int
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the avg_demos table:

insert into avg_demos(c) 
values(10),(20),(30),(null);
Code language: SQL (Structured Query Language) (sql)

Third, view the data of the avg_demos table:

select c 
from avg_demos;
Code language: SQL (Structured Query Language) (sql)

Finally, calculate the average values in the c column of the avg_demos table using the avg() function:

SELECT 
    avg(c) 
FROM 
    avg_demos;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

In this example, the avg() function calculates the average as follows:

• First, ignore the null values.
• Then, divide the sum of all values by the number of values (10+20+30) / 3 = 20.

MariaDB avg() examples

We’ll use the table countries and regions from the sample database.

A) Using MariaDB avg() function to calculate the average of values

The following example uses the avg() function to calculate the average area of all countries:

select
    avg(areas) avg_area
from
    countries;
Code language: SQL (Structured Query Language) (sql)

To make the result more readable, you use the cast expression as follows:

select
    cast(avg(area) as dec(10,0)) avg_area
from
    countries;
Code language: SQL (Structured Query Language) (sql)

The cast truncates all numbers after the decimal point.

B) Using MariaDB avg() function with the group by clause example

The avg() function is often used in conjunction with the group by clause to find the average value for each group.

The following example calculates the average country area of each region:

select
    region_id,
    cast(avg(area) as dec(10,2)) avg_country_area
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 group countries by region id
• Second, the avg() function calculates the average area for each group (or region)

To get the region names, you can join the countries table with the regions table:

select
    regions.name as region,
    cast(avg(area) as dec(10,2)) avg_country_area
from 
    countries
inner join regions 
    using (region_id)
group by 
    regions.name
order by 
    region;
Code language: SQL (Structured Query Language) (sql)

C) Using MariaDB avg() function with the having clause example

The following example uses the avg() function with the group by and having clauses to find regions whose the average country areas are between 200,000 and 1,000,000 km2:

select
    regions.name as region,
    cast(avg(area) as dec(10,2)) avg_country_area
from 
    countries
inner join regions 
    using (region_id)
group by 
    regions.name
having avg(area) 
    between 200000 and 1000000
order by 
    region;
Code language: SQL (Structured Query Language) (sql)

D) Using MariaDB avg() function with other aggregate functions

The following example finds all regions whose the number of countries is greater than 10 and the average country area is greater than 100,000:

select
    regions.name as region,
    count(*) country_count,
    cast(avg(area) as dec(10,2)) avg_country_area
from 
    countries
inner join regions 
    using (region_id)
group by 
    regions.name
having 
	count(*) > 10 and
	avg(area) > 100000
order by 
    avg_country_area;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you learned how to use the MariaDB avg() function to calculate the average value of a set.