# 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.

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.

## Related Functions

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.