SQL COUNT DISTINCT: Syntax, NULLs, and Performance

COUNT(DISTINCT column) counts the number of unique non-NULL values in a column, while plain DISTINCT removes duplicate rows from your results. Both are essential for data analysis, deduplication, and quality checks.

COUNT DISTINCT - Count Unique Values

Find how many unique values exist in a column. The syntax below works unchanged in every major engine — PostgreSQL, MySQL, SQL Server, Oracle, BigQuery, Snowflake, and SQLite:

-- How many unique customers placed orders?
SELECT COUNT(DISTINCT customer_id) as unique_customers
FROM orders;

-- Total orders vs unique customers
SELECT
    COUNT(*) as total_orders,
    COUNT(DISTINCT customer_id) as unique_customers,
    COUNT(*) / COUNT(DISTINCT customer_id) as orders_per_customer
FROM orders;

Does COUNT(DISTINCT) Ignore NULLs?

Yes. COUNT(DISTINCT column) ignores NULLs completely — a NULL is never counted as a distinct value. Only COUNT(*) counts every row regardless of NULLs; both COUNT(column) and COUNT(DISTINCT column) skip rows where the column is NULL. If NULL should count as a category, wrap the column in COALESCE() first.

-- Proof: three values, one NULL, one duplicate
WITH t AS (
    SELECT 'a' AS val UNION ALL
    SELECT 'b'        UNION ALL
    SELECT 'a'        UNION ALL
    SELECT NULL
)
SELECT
    COUNT(*)            AS all_rows,        -- 4 (counts NULLs)
    COUNT(val)          AS non_null_rows,   -- 3 (skips NULL)
    COUNT(DISTINCT val) AS distinct_vals    -- 2 ('a', 'b' — NULL ignored)
FROM t;

-- Count NULL as its own bucket:
SELECT COUNT(DISTINCT COALESCE(val, 'unknown')) FROM t;  -- 3

See handling NULL values in SQL for more NULL gotchas like this one.

How Do You COUNT DISTINCT Across Multiple Columns?

Standard SQL only accepts one expression inside COUNT(DISTINCT ...) in some engines, so counting unique combinations of two or more columns depends on your dialect. PostgreSQL and MySQL accept multiple columns directly; SQL Server does not, so you either concatenate the columns or count a deduplicated subquery — the subquery works everywhere.

-- PostgreSQL (also MySQL): multiple columns / tuple syntax
SELECT COUNT(DISTINCT (country, city)) FROM customers;   -- Postgres tuple
SELECT COUNT(DISTINCT country, city)  FROM customers;    -- MySQL

-- MySQL / SQL Server workaround: concatenate with a separator
SELECT COUNT(DISTINCT CONCAT(country, '|', city)) FROM customers;

-- Portable approach: works in EVERY engine
SELECT COUNT(*) FROM (
    SELECT DISTINCT country, city
    FROM customers
) AS unique_pairs;

Watch out for the CONCAT trick: without a separator, ('ab','c') and ('a','bc') collide. Full details and per-engine syntax in COUNT DISTINCT across multiple columns.

COUNT DISTINCT vs GROUP BY: Which Is Faster?

They answer different questions but can compute the same number. COUNT(DISTINCT col) returns one row; GROUP BY col returns one row per value. For a single total, engines often execute both the same way (hash or sort deduplication), but on very large tables a pre-aggregated subquery can be faster because the deduplication happens before the final count.

-- One number, direct:
SELECT COUNT(DISTINCT customer_id) FROM orders;

-- Same number via GROUP BY subquery (sometimes faster on huge tables):
SELECT COUNT(*) FROM (
    SELECT customer_id FROM orders GROUP BY customer_id
) AS c;

If you need the per-value counts anyway, skip COUNT DISTINCT and use GROUP BY with HAVING directly.

Conditional Counts: COUNT(DISTINCT CASE WHEN ...)

To count distinct values that meet a condition, put a CASE expression inside the count. Rows that fail the condition return NULL, and because COUNT DISTINCT ignores NULLs, they simply drop out — no subquery or extra scan needed. This is the standard way to compute several segmented distinct counts in one pass over the table.

SELECT
    COUNT(DISTINCT customer_id) AS all_buyers,
    COUNT(DISTINCT CASE WHEN total >= 100
                        THEN customer_id END) AS big_spenders,
    COUNT(DISTINCT CASE WHEN order_date >= '2026-01-01'
                        THEN customer_id END) AS buyers_this_year
FROM orders;

Approximate Counts at Scale (APPROX_COUNT_DISTINCT)

Exact COUNT DISTINCT must track every unique value, which gets expensive at billions of rows. Warehouse engines offer HyperLogLog-based approximations that are dramatically faster and typically accurate within 1–2% — ideal for dashboards where "about 4.2M users" is good enough.

-- BigQuery
SELECT APPROX_COUNT_DISTINCT(user_id) FROM events;

-- Snowflake (APPROX_COUNT_DISTINCT is an alias of HLL)
SELECT APPROX_COUNT_DISTINCT(user_id) FROM events;

-- Presto / Trino / Athena
SELECT approx_distinct(user_id) FROM events;

-- SQL Server 2019+
SELECT APPROX_COUNT_DISTINCT(user_id) FROM events;

DISTINCT - Remove Duplicates

Use DISTINCT to return only unique rows:

-- Get list of unique cities
SELECT DISTINCT city
FROM customers
ORDER BY city;

-- Unique combinations of multiple columns
SELECT DISTINCT country, state, city
FROM customers
ORDER BY country, state, city;

Find Unique Values in a Column

-- What payment methods do we accept?
SELECT DISTINCT payment_method
FROM orders
WHERE order_date >= '2024-01-01';

Unique Combinations

-- Find all product-category pairs
SELECT DISTINCT
    category,
    subcategory
FROM products
WHERE active = true
ORDER BY category, subcategory;

COUNT DISTINCT with GROUP BY

Combine COUNT DISTINCT with GROUP BY for powerful analysis:

Customer Reach by Product

SELECT
    product_name,
    COUNT(*) as times_ordered,
    COUNT(DISTINCT customer_id) as unique_buyers,
    SUM(quantity) as total_units_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY product_name
HAVING COUNT(DISTINCT customer_id) >= 10
ORDER BY unique_buyers DESC;

Daily Active Users

SELECT
    DATE(login_time) as date,
    COUNT(DISTINCT user_id) as daily_active_users,
    COUNT(*) as total_logins,
    COUNT(*) / COUNT(DISTINCT user_id) as avg_logins_per_user
FROM user_activity
WHERE login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(login_time)
ORDER BY date;

Multiple COUNT DISTINCT in One Query

SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as total_orders,
    COUNT(DISTINCT customer_id) as unique_customers,
    COUNT(DISTINCT product_id) as unique_products,
    SUM(total) as revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Finding Duplicates

Use DISTINCT counts to identify and analyze duplicate records — see how to count duplicates in SQL for the full find-count-delete workflow:

Detect Duplicate Emails

SELECT
    email,
    COUNT(*) as duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

Find Unique Values Excluding Duplicates

-- Get clean list without duplicates
SELECT DISTINCT email
FROM users
WHERE email IS NOT NULL
    AND email LIKE '%@%.%'
ORDER BY email;

Practical Business Use Cases

Customer Acquisition Report

SELECT
    DATE_TRUNC('week', signup_date) as week,
    COUNT(*) as signups,
    COUNT(DISTINCT email) as unique_emails,
    COUNT(DISTINCT referral_source) as marketing_channels,
    COUNT(*) - COUNT(DISTINCT email) as duplicate_signups
FROM users
WHERE signup_date >= DATE_SUB(CURDATE(), INTERVAL 12 WEEK)
GROUP BY DATE_TRUNC('week', signup_date)
ORDER BY week;

Product Catalog Analysis

SELECT
    category,
    COUNT(*) as total_products,
    COUNT(DISTINCT brand) as unique_brands,
    COUNT(DISTINCT SUBSTRING(sku, 1, 3)) as sku_prefixes,
    MIN(price) as min_price,
    MAX(price) as max_price
FROM products
GROUP BY category
ORDER BY total_products DESC;

Cross-Sell Analysis

-- Customers who bought multiple product categories
SELECT
    customer_id,
    COUNT(DISTINCT category) as categories_purchased,
    COUNT(DISTINCT product_id) as unique_products,
    COUNT(*) as total_purchases,
    SUM(total) as lifetime_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY customer_id
HAVING COUNT(DISTINCT category) >= 3
ORDER BY lifetime_value DESC;

DISTINCT vs GROUP BY

These queries produce the same result:

-- Using DISTINCT
SELECT DISTINCT category
FROM products;

-- Using GROUP BY
SELECT category
FROM products
GROUP BY category;

Use GROUP BY when you also need aggregates (COUNT, SUM, etc.). Use DISTINCT for simple deduplication.