How do you count distinct (customer_id, product_id) pairs portably across SQL engines?

## The question behind the syntax "How many different (customer, product) combinations occur?" is an ordinary business question — distinct pairs of buyer and item, distinct (country, currency) combinations, distinct (user, day) active pairs. Standard `COUNT(DISTINCT ...)` is defined over a single value expression, so counting a *combination* needs either an engine extension or a rewrite. ## The portable rewrite De-duplicate first, count second: ```sql SELECT COUNT(*) AS distinct_pairs FROM ( SELECT DISTINCT customer_id, product_id FROM sales ) AS d; ``` The inner query collapses identical pairs; the outer one counts what is left. Points to remember: most engines require the alias on the derived table, and a `CTE` (`WITH d AS (SELECT DISTINCT …) SELECT COUNT(*) FROM d`) reads better when there are several such measures. This form has no dialect risk and no NULL surprises beyond `DISTINCT`'s own rule that NULLs are duplicates of one another, so a pair such as `(7, NULL)` survives as one combination. ## The engine shortcuts Several engines extend the aggregate: - MySQL accepts multiple arguments: `COUNT(DISTINCT customer_id, product_id)`. Its documentation defines this as the number of rows with different **non-NULL** value combinations, so rows where either column is NULL do not contribute. - PostgreSQL accepts a row constructor: `COUNT(DISTINCT (customer_id, product_id))`, which builds an anonymous row value and de-duplicates on it. - Others, SQL Server among them, take exactly one expression and will reject both spellings. Use a shortcut when the query is deliberately dialect-bound and note it in a comment; use the derived table when the SQL travels — across engines, across an embedded-SQLite test suite, or into a tool that rewrites your query. ## Why the concatenation trick is a trap The folk solution is `COUNT(DISTINCT customer_id || '-' || product_id)`. It fails in three separate ways: 1. **NULL propagation.** Concatenating anything with NULL yields NULL in standard SQL, so a row with a missing product is dropped entirely instead of counting as a combination. 2. **Delimiter collisions.** If the values can contain the delimiter, `('a-b', 'c')` and `('a', 'b-c')` produce the same string and are counted once. With numeric ids this is unlikely; with text keys it is a real defect. 3. **Conversion differences.** Numbers must be cast to text, and engines differ in formatting (padding, decimal separators, locale). Two values that are distinct as numbers can render identically, or vice versa. `||` itself is not universal either — MySQL spells concatenation `CONCAT()` by default. If you must build a key, cast explicitly and pick a delimiter the data cannot contain — but prefer the derived table, which needs none of that reasoning. ## The NULL policy is a decision, not a detail The two approaches genuinely disagree about pairs containing NULL, so decide what the metric means before choosing. If a missing product id means "no product", excluding it is right and the MySQL-documented behaviour matches. If it means "unclassified", you want it counted, and the derived table gives you that. Make the intent visible: filter the NULLs out explicitly with a `WHERE` clause, or coalesce them to a sentinel, so the next reader does not have to know one engine's aggregate rules to understand the number. ## Sanity check Distinct pairs must be at least as many as the distinct values of either column alone and at most the row count. `COUNT(DISTINCT a) + COUNT(DISTINCT b)` is not a bound on anything and is never the answer — that mistake shows up in reviews often enough to be worth a test with a three-row fixture. ## Answering it well Lead with the portable derived table, mention that engine shortcuts exist but differ, name the NULL asymmetry as a decision, and reject the concatenation trick with a specific reason rather than a general dislike.