MariaDB COUNT Function [10 Examples]

In this MariaDB tutorial, we will discuss how to use the MariaDB Count function and we’ll look at several examples related to it. There are lists of the topic that comes under discussion:

• MariaDB Count
• MariaDB Count Distinct
• MariaDB Count If
• MariaDB Count Group By
• MariaDB Count Character in String
• MariaDB Count Over
• MariaDB Count Table Rows
• MariaDB Count with Condition
• MariaDB Count Rows in All Tables
• MariaDB Count Number of Rows

MariaDB Count

In this sub-topic, we will learn about the MariaDB Count in detail and which is explained with the help of syntax and an example.

The COUNT function in MariaDB returns the number of times an expression has been used. The syntax of the COUNT function in MariaDB is given below:

SYNTAX:

SELECT COUNT(AGGREGATE_FUNC)
FROM TABLE_NAME 
WHERE [condition];

OR, when grouping the results by one or more columns, the syntax for the COUNT function is:

SELECT EXPRESSION_1,EXPRESSION_2,EXPRESSION_N,
COUNT(AGGREGATE_FUNC)
FROM TABLE_NAME
WHERE [condition]
GROUP BY EXPRESSION_1,EXPRESSION_2,EXPRESSION_N;

The syntax is explained below in detail:

• EXPRESSION_1, EXPRESSION_2, EXPRESSSION_N: Expressions that are not wrapped within the COUNT function and must be included in the SQL statement’s GROUP BY clause.
• AGGREGATE_FUNC: In the column or expression which contains non-null values will be counted.
• TABLES_NAME: The tables from which we wish to retrieve data. There should be at least one table in the FROM clause.
• WHERE [conditions]: These are the criteria that must be met for the recordings to be chosen.

First, let’s have a look at the EMPLOYEE table by the following query:

SELECT * FROM EMPLOYEE;

In the preceding query, we have retrieved all records from the EMPLOYEE table by using the SELECT statement.

Here is the illustrated example of the COUNT function in the EMPLOYEE table by the following query:

EXAMPLE:

SELECT COUNT(last_name) as Total_LastName FROM employee
WHERE emp_gender='Female';

• In the query, we have used the COUNT function on the last_name column as in to count the total number of last_name in the EMPLOYEE table and given an alias name called TOTAL_LASTNAME.
• And this is done based on the WHERE condition to retrieve all records where the emp_gender column is FEMALE.
• So, based on this query by the COUNT function, it will count the total number of employees’ last names in the last_name column of the EMPLOYEE table.

Read: What is MariaDB Sharding

MariaDB Count Distinct

In this sub-topic, we will learn how to use the DISTINCT clause in the COUNT function of the MariaDB and which is explained with the help of syntax and illustrated example.

In MariaDB, the DISTINCT clause is used to remove duplicates from the result in the SELECT statement. The syntax to use the DISTINCT clause in the COUNT function is given below:

SYNTAX:

SELECT COUNT(DISTINCT expression) FROM TABLE_NAME
WHERE [CONDITION];

Here is the illustrated example of how to use the DISTINCT clause in the COUNT function in MariaDB.

EXAMPLE:

SELECT COUNT(DISTINCT first_name) AS Total_unique_FirstName FROM employee
WHERE emp_gender='Male';

In the preceding query, we have counted the unique names given in the first_name column by using the DISTINCT clause inside the COUNT function. And put the result as an alias name called Total_unique_FirstName which was done by using the WHERE condition as the emp_gender column has Male records.

So, as the result, it will count how many unique first_name are there in the EMPLOYEE table which are having the Male as in the EMP_GENDER column. The output signifies 15 records from the EMPLOYEE table as a result.

Read: MariaDB Reserved Words

MariaDB Count If

In this section, we will learn how to use the IF condition with the COUNT function in the MariaDB query and which is explained with the help of syntax and illustrated example.

In MariaDB, the COUNT function is used to get the number of values from a MariaDB table or expression. The syntax to use the IF condition with the COUNT function is given below:

SYNTAX:

SELECT [DISTINCT] COUNT([DISTINCT] IF(CONDITION,EXPRESSION,NULL) 
AS ALIAS_NAME
FROM TABLE_NAME;

The explanation of syntax:

• The COUNT() function includes the IF condition which has a condition specified.
• If the CONDITION is true, the count will be calculated using the expression that has been passed. Otherwise, the count function returns NULL. If NULL is supplied to count, it will return the count of the null values in the column column_name instead of the count of the results.
• Using the DISTINCT keyword is optional.
• The alias_name is the name that is given to the alias result.
• The table_name is the name from which we want to get the count of the column.

Here is the illustrated example of using the IF condition with the COUNT function in the EMPLOYEE table by the following query:

EXAMPLE:

SELECT DISTINCT COUNT(DISTINCT IF(EMP_GENDER='MALE',first_name,NULL)) AS COUNTOFMALE_EMPLOYEE
FROM employee;

If the EMP_GENDER = ‘MALE,’ we use the count() function on the first_name column to calculate the count of separate GENDER. If the gender is something other than ‘Male,’ the gender count will not be calculated.

Read: MariaDB Rename Table

MariaDB Count Group By

We’ll learn how to use the GROUP BY clause in the COUNT function in MariaDB, which is described using syntax and an illustrated example.

In a SELECT statement, the MariaDB GROUP BY clause is used to collect data from numerous records and band the results with one or more columns. Here is the illustrated syntax of the GROUP BY clause with the COUNT function:

SYNTAX:

SELECT COUNT(expression) FROM TABLE_NAME
WHERE [CONDITION]
GROUP BY column_name;

Let’s have the sample example of using the COUNT function with the GROUP BY clause by the following query:

EXAMPLE:

SELECT emp_id,COUNT(*) AS 'Number of Employee' FROM employee 
WHERE emp_gender IN ('Male','Female')
GROUP BY emp_id;

Because the emp_id column is not wrapped in the COUNT function, we must use a GROUP BY clause in this COUNT function example. As a result, the emp_id column must be specified in the GROUP BY section of the MariaDB query.

Read: MariaDB If Null + Examples

MariaDB Count Character in String

In this section, we will learn how to count characters in strings in MariaDB and we will explain it using syntax and an example.

We may utilize the logic of subtraction with length to count the number of instances of a string in a VARCHAR. The illustrated example is to count characters in strings by the following query:

EXAMPLE:

SELECT emp_id,emp_email,
ROUND (
 (
 LENGTH(emp_email)- LENGTH( REPLACE (emp_email, '.com','') )
	) / LENGTH('.com')
) AS NumberOfOccurrenceOf_COM from Employee;

In this query, we will use the length function inside the round function to see the occurrences of the string “.com”. The result will be get displayed in the NumberofOccurrenceOf_COM column from the EMPLOYEE table.

Read: MariaDB Drop Table + Examples

MariaDB Count Over

In this tutorial, we will understand and learn about the OVER function with the COUNT function and which is explained with the help of illustrated syntax and an example.

In MariaDB, the PARTITION BY clause is used to reduce the window for a particular group within the dataset. The illustrated example of using the PARTITION BY clause in the OVER function with the COUNT() function is given below by the following query:

EXAMPLE:

SELECT distinct emp_gender, COUNT(emp_gender) over(PARTITION by emp_gender) AS gender_valuation 
FROM employee;

In the table, out of 30 records carry 15 rows of Male, 14 rows of females, and 1 row of non-binary in the EMPLOYEE table. So, in the preceding query, we have used the DISTINCT clause on the EMP_GENDER table.

And we have counted the EMP_GENDER column by using the COUNT function which is done based on the partition by Male, Female, and non-binary for using the PARTITION BY clause on the EMP_GENDER column.

And the output is called GENDER_VALUATION by using the ALIAS_NAME clause from the EMPLOYEE table. All these functions and clauses are done by using the SELECT statement.

Read: MariaDB Foreign Key + Examples

MariaDB Count with Condition

In this section, we will discuss the use of the COUNT function in MariaDB with any condition of your choice and we’ll illustrate it with the help of an example.

Here is the illustrated example of the COUNT function with the condition by the following query:

EXAMPLE:

SELECT distinct emp_gender, COUNT(emp_gender) over(PARTITION by emp_gender) AS gender_valuation 
FROM employee
WHERE EMP_ID>=15;

In the table, 30 records carry 15 rows of Male, 14 rows of females, and 1 row of non-binary in the EMPLOYEE table. So, in the preceding query, we have used the DISTINCT clause on the EMP_GENDER table.

And we have counted the EMP_GENDER column by using the COUNT function which is done based on the partition by Male, Female, and non-binary for using the PARTITION BY clause on the EMP_GENDER column.

And the output is called GENDER_VALUATION by using the ALIAS_NAME clause from the EMPLOYEE table. Now, the value of the output will be changed based on the WHERE condition to retrieve all records where the value of the emp_id column is greater than or equal to 15.

This means that the query will provide a different value for Male, Female, and Non_Binary as (8,7,1) in the output respectively. All these functions and clauses are implemented using the SELECT statement.

Read: MariaDB Temporary Table

MariaDB Count Rows in All Tables

In this section, we will count rows in all tables for that we can use the TABLE_ROWS with the aggregate function SUM. The table_rows is used to count the number of rows. The syntax is as follows:/

SYNTAX:

SELECT SUM(TABLE_ROWS)
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = 'DATABASE_NAME';

The illustrated example is to count rows in all tables by using the following query:

EXAMPLE:

SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'airbnb_db';

The SELECT statement example brings the total number of rows from all the tables as 5,123 by using the INFORMATION_SCHEMA clause on the AIRBNB_dB database.

Read: MariaDB Reset Root Password

MariaDB Count Table Rows

In this section, we will count table_rows with the help of the table_rows keyword and which is explained with the help of illustrated syntax and an example.

Suppose, the user wants to count several rows per table, then we will use the below following query:

SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'airbnb_db';

In this query, with the help of the airbnb_db database, we have counted several rows per table by using the SELECT statement on the information_schema method. And selecting table_name and table_rows from the airbnb_db database.

Read: MariaDB Backup Database

MariaDB Count Number of Rows

In this section, we will understand and learn about the COUNT function to count several rows from a MariaDB table.

Here is the illustrated example to count the number of rows by using the COUNT function by the following query:

EXAMPLE:

SELECT COUNT(*) FROM EMPLOYEE;

In the preceding query, we have counted all the records from the EMPLOYEE table by using the asterisk as * by using the COUNT function in the query for the resultset.

You may also like to read the following MariaDB tutorials.

• MariaDB Enable Remote Access
• MariaDB Select Statement
• MariaDB Show Column Data Type
• MariaDB Truncate Table + Examples
• MariaDB Rename Column + Examples

In this MariaDB tutorial, we understood how to use the MariaDB COUNT function and also look at some sample examples. There are lists of the topic that comes under discussion:

• MariaDB Count
• MariaDB Count Distinct
• MariaDB Count If
• MariaDB Count Group By
• MariaDB Count Character in String
• MariaDB Count Over
• MariaDB Count Table Rows
• MariaDB Count with Condition
• MariaDB Count Rows in All Tables
• MariaDB Count Number of Rows