Skip to main content

multi_distinct_count

Description​

Returns the total number of rows of the expr, equivalent to count(distinct expr).

Syntax​

multi_distinct_count(expr)

Parameters​

expr: the column or expression based on which multi_distinct_count() is performed. If expr is a column name, the column can be of any data type.

Return value​

Returns a numeric value. If no rows can be found, 0 is returned. This function ignores NULL values.

Examples​

Suppose there is a table named test. Query the category and supplier of each order by id.

select * from test order by id;
+------+----------+----------+------------+
| id   | country  | category | supplier   |
+------+----------+----------+------------+
| 1001 | US       | A        | supplier_1 |
| 1002 | Thailand | A        | supplier_2 |
| 1003 | Turkey   | B        | supplier_3 |
| 1004 | US       | A        | supplier_2 |
| 1005 | China    | C        | supplier_4 |
| 1006 | Japan    | D        | supplier_3 |
| 1007 | Japan    | NULL     | supplier_5 |
+------+----------+----------+------------+

Example 1: Count the number of distinct values in the category column.

select multi_distinct_count(category) from test;
+--------------------------------+
| multi_distinct_count(category) |
+--------------------------------+
|                              4 |
+--------------------------------+

Example 2: Count the number of distinct values in the supplier column.

select multi_distinct_count(supplier) from test;
+--------------------------------+
| multi_distinct_count(supplier) |
+--------------------------------+
|                              5 |
+--------------------------------+