How the COUNT() function works in Mariadb?

The COUNT() function is an aggregate function that returns the number of rows in a table or a group of rows. The function can be used with or without a column or expression argument.

Posted on

The COUNT() function is an aggregate function that returns the number of rows in a table or a group of rows. The function can be used with or without a column or expression argument. The function counts all rows, including NULL values and duplicates.

Syntax

The syntax of the COUNT() function is as follows:

COUNT([DISTINCT] expr)

The function takes one optional argument, expr, which is the column or expression to be counted. The argument can be a string, a number, a date, a time, or a binary value. The function returns an integer value that represents the number of rows in the argument. If the argument is omitted, the function returns the number of rows in the table or the group. If the argument is preceded by the keyword DISTINCT, the function returns the number of distinct values in the argument, ignoring NULL values and duplicates.

Examples

Example 1: Counting the number of rows in a table

The following example uses the COUNT() function to count the number of rows in the students table.

SELECT COUNT(*) FROM students;

The output is:

+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+

The output shows that there are six rows in the students table.

Example 2: Counting the number of rows in a column

The following example uses the COUNT() function to count the number of rows in the name column of the students table.

SELECT COUNT(name) FROM students;

The output is:

+------------+
| COUNT(name) |
+------------+
|          6 |
+------------+

The output shows that there are six rows in the name column of the students table. The function counts all rows, including the NULL value.

Example 3: Counting the number of rows in an expression

The following example uses the COUNT() function to count the number of rows in the expression name || ' ' || grade of the students table. The expression concatenates the name and the grade columns with a space in between.

SELECT COUNT(name || ' ' || grade) FROM students;

The output is:

+---------------------------------+
| COUNT(name || ' ' || grade) |
+---------------------------------+
|                               5 |
+---------------------------------+

The output shows that there are five rows in the expression name || ' ' || grade of the students table. The function does not count the row with the NULL value in the name column.

Example 4: Counting the number of rows in a column with a condition

The following example uses the COUNT() function to count the number of rows in the name column of the students table, where the grade column is greater than or equal to 80.

SELECT COUNT(name) FROM students WHERE grade >= 80;

The output is:

+------------+
| COUNT(name) |
+------------+
|          4 |
+------------+

The output shows that there are four rows in the name column of the students table, where the grade column is greater than or equal to 80. The function counts all rows that satisfy the condition, including the NULL value.

Example 5: Counting the number of rows in a column with a group by clause

The following example uses the COUNT() function to count the number of rows in the name column of the students table, grouped by the grade column.

SELECT grade, COUNT(name) FROM students GROUP BY grade;

The output is:

+-------+------------+
| grade | COUNT(name) |
+-------+------------+
|    70 |          1 |
|    80 |          2 |
|    90 |          1 |
|   100 |          1 |
|  NULL |          1 |
+-------+------------+

The output shows that there are one row in the name column of the students table for each grade of 70, 90, and 100, two rows for the grade of 80, and one row for the NULL grade. The function counts all rows in each group, including the NULL values.

There are some other functions that are related to the COUNT() function in Mariadb. They are:

  • COUNT DISTINCT: This function returns the number of distinct values in a column or expression in a table or a group of rows. The function ignores NULL values and duplicates.
  • SUM(): This function returns the sum of a column or expression in a table or a group of rows. The function can be used with a numeric column or expression. The function ignores NULL values and duplicates.
  • AVG(): This function returns the average of a column or expression in a table or a group of rows. The function can be used with a numeric column or expression. The function ignores NULL values and duplicates.
  • MIN(): This function returns the minimum value of a column or expression in a table or a group of rows. The function can be used with any type of column or expression. The function ignores NULL values and duplicates.
  • MAX(): This function returns the maximum value of a column or expression in a table or a group of rows. The function can be used with any type of column or expression. The function ignores NULL values and duplicates.

Conclusion

The COUNT() function is a useful function to count the number of rows in a table or a group of rows. The function can be used with or without a column or expression argument. The function counts all rows, including NULL values and duplicates. It is similar to the COUNT DISTINCT function, but with a different counting criterion. It is also related to some other functions that provide aggregate operations on columns or expressions.