How to COUNT DISTINCT Multiple Columns in SQL

Counting distinct combinations of two or more columns is one of those tasks where every database has a slightly different answer. PostgreSQL accepts a tuple inside COUNT(DISTINCT ...), MySQL accepts a comma-separated list, and SQL Server accepts neither — which is why the subquery approach is the one pattern worth memorizing.

What is the portable way to count distinct combinations?

Wrap a SELECT DISTINCT over the columns you care about in a derived table, then count its rows. This works identically in PostgreSQL, MySQL, SQL Server, Oracle, SQLite, BigQuery, and Snowflake, so it is the version to use in shared codebases and BI tools that hit multiple engines.

-- Works everywhere
SELECT COUNT(*) AS distinct_pairs
FROM (
    SELECT DISTINCT customer_id, product_id
    FROM orders
) AS unique_combos;

A useful property of this form: rows where both columns are NULL still produce one distinct row, because SELECT DISTINCT treats NULLs as equal to each other for deduplication. Direct COUNT(DISTINCT ...) forms drop rows containing NULLs entirely, so the two approaches can return different numbers on messy data.

Does PostgreSQL support COUNT(DISTINCT) on multiple columns?

Yes. PostgreSQL lets you build an anonymous row value (a tuple) with parentheses and count distinct tuples directly. The extra parentheses are required — without them PostgreSQL raises a syntax error, because standard COUNT(DISTINCT x) only takes a single expression.

-- PostgreSQL: note the inner parentheses
SELECT COUNT(DISTINCT (customer_id, product_id)) AS distinct_pairs
FROM orders;

-- Without inner parens this fails in PostgreSQL:
-- SELECT COUNT(DISTINCT customer_id, product_id) FROM orders;  -- ERROR

One subtlety: a tuple like (1, NULL) is not considered NULL as a whole, so PostgreSQL's tuple form keeps rows where only some columns are NULL — matching the subquery approach more closely than MySQL's behavior does.

How does MySQL handle COUNT DISTINCT with multiple columns?

MySQL extends COUNT(DISTINCT) to accept a comma-separated list of expressions — no extra parentheses needed. However, MySQL ignores any row in which any of the listed expressions is NULL, which silently shrinks your count on nullable columns.

-- MySQL: comma-separated list, no tuple parens
SELECT COUNT(DISTINCT customer_id, product_id) AS distinct_pairs
FROM orders;

-- Rows where customer_id OR product_id is NULL are NOT counted.
-- Compare against the subquery version to see the difference:
SELECT COUNT(*) FROM (
    SELECT DISTINCT customer_id, product_id FROM orders
) AS x;

Why is CONCAT a risky workaround?

A popular trick is to concatenate the columns and count distinct strings. It appears to work but has two failure modes: value collisions and NULL propagation. Concatenation can map different combinations to the same string, and in most engines concatenating a NULL yields NULL, which the count then drops.

-- DANGEROUS: collision example
-- ('ab', 'c')  -> 'abc'
-- ('a', 'bc')  -> 'abc'   -- different pair, same string!
SELECT COUNT(DISTINCT CONCAT(col_a, col_b)) FROM t;

-- Safer if you must concatenate: add a separator that
-- cannot appear in the data, and handle NULLs explicitly
SELECT COUNT(DISTINCT CONCAT(
    COALESCE(col_a, '~null~'), '|',
    COALESCE(col_b, '~null~')
)) FROM t;

Even the "safe" version depends on the separator never occurring in real values, and string building makes the aggregate slower on large tables. Treat CONCAT counting as a last resort for engines with no better option.

Which syntax works in which database?

Engine	COUNT(DISTINCT (a,b)) tuple	COUNT(DISTINCT a,b) list	Subquery over SELECT DISTINCT
PostgreSQL	Yes	No	Yes
MySQL / MariaDB	No	Yes	Yes
SQL Server	No	No	Yes
Oracle	No	No	Yes
SQLite	No	No	Yes
BigQuery	No (use STRUCT via subquery)	No	Yes
Snowflake	No	Yes (NULL-dropping)	Yes

If you only remember one row of that table, remember the last column: the subquery form is the lingua franca. For the single-column basics — including how COUNT(DISTINCT col) treats NULLs — see our guide to DISTINCT and COUNT DISTINCT in SQL.

How do I count distinct combinations per group?

Combine either syntax with GROUP BY. With the subquery approach, do the DISTINCT inside and the grouping outside, which also tends to produce a friendlier execution plan than nesting a grouped DISTINCT aggregate.

-- Distinct (product, day) combinations per customer
SELECT customer_id, COUNT(*) AS combos
FROM (
    SELECT DISTINCT customer_id, product_id, order_date
    FROM orders
) AS x
GROUP BY customer_id
ORDER BY combos DESC;