How the COUNT() function works in Mariadb?

The MariaDB COUNT() function is used to count the number of rows that match a specified condition or retrieve the number of non-NULL values in a particular column.

Syntax

The syntax for the MariaDB COUNT() function is as follows:

COUNT(expression)

• expression (optional): This is the column or expression to be evaluated. If no expression is provided, COUNT(*) will count the total number of rows, including rows with NULL values.

Examples

Example 1: Count All Rows

This example demonstrates how to count all rows in a table using COUNT(*).

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

INSERT INTO employees (id, name, age) VALUES
    (1, 'John', 25),
    (2, 'Jane', 30),
    (3, 'Bob', NULL);

SELECT COUNT(*) FROM employees;

The output will be:

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

This shows that there are 3 rows in the employees table, including the row with a NULL value for the age column.

Example 2: Count Non-NULL Values

This example counts the number of non-NULL values in a specific column.

SELECT COUNT(age) FROM employees;

The output will be:

+------------+
| COUNT(age) |
+------------+
|          2 |
+------------+

This output indicates that there are 2 non-NULL values in the age column of the employees table.

Example 3: Count Distinct Values

This example demonstrates how to count the number of distinct values in a column.

INSERT INTO employees (id, name, age) VALUES (4, 'Alice', 30);
SELECT COUNT(DISTINCT age) FROM employees;

The output will be:

2

This shows that there are 2 distinct age values (25 and 30) in the employees table.

Example 4: Count with Condition

This example counts the number of rows that match a specific condition.

SELECT COUNT(*) FROM employees WHERE age > 25;

The output will be:

+---------------------+
| COUNT(DISTINCT age) |
+---------------------+
|                   2 |
+---------------------+

This output indicates that there are 2 rows in the employees table where the age value is greater than 25.

Example 5: Count with Grouping

This example demonstrates how to use COUNT() with grouping.

SELECT name, COUNT(*) FROM employees GROUP BY name;

The output will be:

+-------+----------+
| name  | COUNT(*) |
+-------+----------+
| Alice |        1 |
| Bob   |        1 |
| Jane  |        1 |
| John  |        1 |
+-------+----------+

This output shows the count of rows for each distinct name value in the employees table.

Related Functions

The following are a few functions related to the MariaDB COUNT() function:

• MariaDB SUM() function is used to calculate the sum of values in a column.
• MariaDB AVG() function is used to calculate the average value of a column.
• MariaDB MAX() function is used to retrieve the maximum value from a column.
• MariaDB MIN() function is used to retrieve the minimum value from a column.

Conclusion

The MariaDB COUNT() function is a powerful tool for counting rows or non-NULL values in a table or a specific column. It can be used with various clauses and conditions to filter and group the data as needed. Understanding how to use COUNT() effectively can greatly aid in data analysis and reporting tasks.