How the COUNT-DISTINCT() function works in Mariadb?

The COUNT DISTINCT is an aggregate function that returns the number of distinct values in a given column or expression.

Posted on

The COUNT DISTINCT is an aggregate function that returns the number of distinct values in a given column or expression. The function can be used to count the number of unique values in a table or a group of rows. The function ignores NULL values and duplicates.

Syntax

The syntax of the COUNT DISTINCT function is as follows:

COUNT(DISTINCT expr)

The function takes one 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 distinct values in the argument.

Examples

Example 1: Counting the number of distinct values in a column

The following example uses the COUNT DISTINCT function to count the number of distinct values in the name column of the students table.

SELECT COUNT(DISTINCT name) FROM students;

The output is:

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

The output shows that there are four distinct values in the name column of the students table.

Example 2: Counting the number of distinct values in an expression

The following example uses the COUNT DISTINCT function to count the number of distinct values 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(DISTINCT name || ' ' || grade) FROM students;

The output is:

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

The output shows that there are five distinct values in the expression name || ' ' || grade of the students table.

Example 3: Counting the number of distinct values in a column with a condition

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

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

The output is:

+----------------------+
| COUNT(DISTINCT name) |
+----------------------+
|                    3 |
+----------------------+

The output shows that there are three distinct values in the name column of the students table, where the grade column is greater than or equal to 80.

Example 4: Counting the number of distinct values in a column with a group by clause

The following example uses the COUNT DISTINCT function to count the number of distinct values in the name column of the students table, grouped by the grade column.

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

The output is:

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

The output shows that there are one distinct value in the name column of the students table for each grade of 70, 90, and 100, and two distinct values for the grade of 80.

Example 5: Counting the number of distinct values in a column with a NULL value

The following example uses the COUNT DISTINCT function to count the number of distinct values in the name column of the students table, where one of the values is NULL.

SELECT COUNT(DISTINCT name) FROM students;

The output is:

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

The output shows that there are four distinct values in the name column of the students table, excluding the NULL value.

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

  • COUNT(): This function 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.
  • 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 DISTINCT function is a useful function to count the number of distinct values in a column or expression in a table or a group of rows. The function returns an integer value that represents the number of distinct values in the argument. The function ignores NULL values and duplicates. It is similar to the COUNT() function, but with a different counting criterion. It is also related to some other functions that provide aggregate operations on columns or expressions.