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

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.

## Related Functions

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.