MariaDB COUNT(DISTINCT): Unique Values, Multiple Columns and NULL
Learn MariaDB COUNT(DISTINCT) for unique values and combinations, how it handles NULL and empty results, and how it differs from SELECT DISTINCT.
On this page
MariaDB COUNT(DISTINCT expression) counts the number of different, non-NULL values in a group. It is useful for counting unique users, products, or categories without counting repeated rows.
Unlike some other database systems, MariaDB also supports COUNT(DISTINCT expression1, expression2, ...) to count distinct combinations of multiple expressions.
Syntax
COUNT(DISTINCT expression)
COUNT(DISTINCT expression1, expression2, ...)
- A repeated value counts only once.
- SQL
NULLdoes not contribute to the count. - For multiple expressions, combinations with a
NULLin any expression are not counted. - The result is an integer. An empty input set or a set with no qualifying non-NULL values returns
0.
Examples
Count different values in one column
SELECT
COUNT(*) AS all_rows,
COUNT(value) AS non_null_rows,
COUNT(DISTINCT value) AS different_values
FROM (
SELECT 1 AS value
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT NULL
) AS examples;
all_rows non_null_rows different_values
-------- ------------- ----------------
4 3 2There are four input rows, three non-NULL values, and only two different non-NULL values: 1 and 2.
Count unique products by store
WITH sales AS (
SELECT 1 AS store_id, 101 AS product_id
UNION ALL SELECT 1, 101
UNION ALL SELECT 1, 102
UNION ALL SELECT 2, 102
)
SELECT
store_id,
COUNT(DISTINCT product_id) AS unique_products
FROM sales
GROUP BY store_id
ORDER BY store_id;
store_id unique_products
-------- ---------------
1 2
2 1GROUP BY creates one result per store. COUNT(DISTINCT product_id) removes duplicate products within that store’s group.
Count distinct combinations of two columns
SELECT COUNT(DISTINCT customer_id, product_id) AS distinct_pairs
FROM (
SELECT 1 AS customer_id, 10 AS product_id
UNION ALL SELECT 1, 10
UNION ALL SELECT 1, 11
UNION ALL SELECT 2, NULL
) AS purchases;
distinct_pairs
--------------
2The unique complete pairs are (1, 10) and (1, 11). The row (2, NULL) does not contribute because one component is SQL NULL.
Do not simulate multi-column uniqueness by concatenating fields without careful delimiters and NULL handling: distinct tuples can collapse to the same concatenated text.
COUNT(DISTINCT) versus SELECT DISTINCT
These statements answer different questions:
| Expression | Meaning |
|---|---|
COUNT(*) |
Count every input row, including rows with NULLs |
COUNT(column) |
Count rows where the column is not NULL |
COUNT(DISTINCT column) |
Count distinct non-NULL values |
SELECT DISTINCT column |
Return the unique column values as result rows, potentially including a NULL row |
For example, SELECT DISTINCT value over 1, 1, 2, NULL returns three rows: 1, 2 and NULL. But COUNT(DISTINCT value) returns 2 because it does not count the NULL value.
For multiple columns, SELECT DISTINCT a, b can retain a row containing NULL, whereas COUNT(DISTINCT a, b) does not count such a combination.
Common mistakes
Why is my COUNT(DISTINCT) lower than expected?
Check whether the input contains SQL NULL, whether duplicates are present, and whether your query’s WHERE conditions remove rows. For strings, the column’s collation also affects which values compare as equal. If case sensitivity is important, verify the collation instead of assuming 'Alice' and 'alice' are always distinct.
Does COUNT(DISTINCT) return NULL on an empty set?
No. It returns 0. Other aggregates, such as SUM() or AVG(), can return NULL for an empty set.
Is COUNT(DISTINCT col1, col2) supported everywhere?
No. MariaDB supports a list of expressions; other SQL database engines have different multi-column DISTINCT syntax or require a subquery. Avoid copying this syntax to PostgreSQL, SQLite, or SQL Server without checking the target engine.
Is COUNT(DISTINCT) expensive?
It may require deduplication of many rows. Appropriate indexes and query shape can help, but the optimizer’s plan depends on the data and MariaDB version. Inspect EXPLAIN before applying performance changes.
For exact syntax and examples, see the official MariaDB COUNT DISTINCT documentation.