Menu

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.

Posted on By Updated on
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 NULL does not contribute to the count.
  • For multiple expressions, combinations with a NULL in 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              2

There 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         1

GROUP 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
--------------
2

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