MariaDB sum() Function

Summary: in this tutorial, you will learn how to use the MariaDB sum() function to calculate the sum of values.

Introduction to MariaDB sum() function

The MariaDB sum() is an aggregate function that returns the sum of numbers.

Here is the syntax of the sum() function:

sum(all | distinct expression)
Code language: SQL (Structured Query Language) (sql)

The sum() function takes a set of numbers and returns a total of them.

The distinct option instructs the sum() function to ignore duplicates and calculates the total of distinct values while the all option enables the sum() function to calculate the total of values including duplicates. By default, the sum() function uses the all option.

If the set of input numbers is empty or all values in the set are null, the sum() function returns null.

Similar to the avg() function, the sum() function ignores null values.

MariaDB sum() function illustration

First, create a new table called sum_demos:

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

Second, insert some rows into the sum_demos table:

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

Third, query data from the sum_demos table:

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

Fourth, use the sum() function to return the sum of values, including duplicates, in the c column of the sum_demos table:

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

In this example, the sum() function calculates the total of all values including the duplicates because it uses the all option by default.

Finally, use the distinct option in the sum() function to calculate the sum of distinct values.

select sum(distinct c)
from sum_demos;
Code language: SQL (Structured Query Language) (sql)

In this example, the sum() ignores one duplicate value (30) in its calculation because of the distinct option.

MariaDB sum() function examples

We’ll use the table countries and regions for the demonstration.

A) Using MariaDB sum() function to calculate the sum of values

This example uses the sum() function to calculate the total areas of all countries in the countries table:

select sum(area)
from countries;
Code language: SQL (Structured Query Language) (sql)

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

The sum() function is often used in conjunction with the group by clause to calculate the total for each group.

For example, the following statement returns the total area for each region:

select 
    region_id,
    sum(area) region_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 divides the countries by regions.
• Then, the sum() function calculates the total areas for each region.

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

select 
    regions.name region,
    sum(area) region_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 sum() function with the having clause

The following statement uses the sum() function in the having clause to find regions whose total areas are greater than 10,000,000 km2:

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

In this tutorial, you learned how to use the MariaDB sum() function to calculate the sum of values.