MariaDB count() Function

Summary: in this tutorial, you will learn how to use the MariaDB count() function to return the number of rows in a table.

An overview of the MariaDB count() function

The MariaDB count() is an aggregate function that returns the number of rows in a table.

Here is the syntax of the count() function:

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

The count() function accepts a set of rows and returns the number of rows in the set.

The count(expression) is the same as count(all expression) that returns the number of non-null values including duplicates.

The count(distinct expression) returns the number of unique non-null values.

The count(*) returns the number of values including nulls.

MariaDB count() function illustration

First, create a new table called count_demos:

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

Second, insert some rows into the count_demos table:

insert into count_demos(c) 
values(1),(2),(3),(null),(3);
Code language: SQL (Structured Query Language) (sql)

Third, query data from the count_demos table:

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

Fourth, the following statement uses the count(*) function to return the number of rows in the count_demos table:

select 
    count(*) row_count
from 
    count_demos;
Code language: SQL (Structured Query Language) (sql)

Fifth, this example uses the count(distinct expression) to return the number of non-null rows in the count_demos table:

select
    count(c) row_count
from 
    count_demos;
Code language: SQL (Structured Query Language) (sql)

Sixth, this statement uses the count(distinct expression) to return the number of unique and non-null rows from the count_demos table:

select
    count(distinct c) row_count
from 
    count_demos;
Code language: SQL (Structured Query Language) (sql)

MariaDB count() function examples

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

A) Using MariaDB count(*) function to return the number of rows in a table

This example uses the count(*) function to get the number of rows in the countries table:

select 
    count(*) country_count
from
    countries;
Code language: SQL (Structured Query Language) (sql)

To get the number of countries that have national day recorded, you use a where clause:

select     
    count(*) country_count
from
    countries
where 
    national_day is not null;
Code language: SQL (Structured Query Language) (sql)

B) Using the MariaDB count() function with a group by clause

The count() function is often used with a group by clause to find the number of values for each group.

For example, the following statement uses the count(expression) to find the number of countries in each region:

select
    region_id,
    count(country_id) country_count
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 countries by region id into groups.
• Second, the count() function returns the number of countries in each group.

To get the region names and the number of countries in each region, you add a join clause to the above query:

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

B) Using the MariaDB count() function with a having clause

The following statement uses the count() function in the having clause to find regions that have more than 10 countries:

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

In this tutorial, you learned how to use the MariaDB count() function to find the number of rows in a table.