Home/Learn/SQL/COUNT(DISTINCT col) & Approximate Counting

COUNT(DISTINCT col) & Approximate Counting

Advanced

Aggregation & Grouping

COUNT(DISTINCT col) counts unique non-NULL values but is expensive on large tables; approximate algorithms like HyperLogLog offer near-linear performance with small, configurable error.

Overview

COUNT(DISTINCT col) ignores NULLs, deduplicate values, and returns the exact cardinality. On large tables it requires building an in-memory hash set of all distinct values — memory-intensive and unparallelisable in many engines. For analytics at scale (billions of rows), HyperLogLog (HLL) provides approximate distinct counts with a tunable error rate (typically 0.8–2%), using kilobytes of memory regardless of cardinality. PostgreSQL supports HLL via the pg_hll extension; BigQuery provides APPROX_COUNT_DISTINCT natively. Counting distinct combinations across multiple columns requires a workaround such as hashing or concatenation. Understanding NULL behaviour is critical: COUNT(*) counts all rows, COUNT(col) counts non-NULLs, COUNT(DISTINCT col) counts distinct non-NULLs.

NULL Behaviour and Multi-Column Distinct Count

COUNT(DISTINCT col) silently excludes NULLs. To count distinct combinations of multiple columns, use a subquery or a hash/concatenation workaround — there is no native COUNT(DISTINCT a, b) syntax in standard SQL.

SQL — NULL behaviour, multi-column distinct count workarounds

-- NULL behaviour
-- Suppose orders.product_id has some NULLs:
INSERT INTO orders (user_id, product_id, amount, status, created_at)
VALUES (1, NULL, 50.00, 'pending', NOW());

SELECT
    COUNT(*)                    AS total_rows,       -- counts NULLs too
    COUNT(product_id)           AS non_null_products, -- excludes NULLs
    COUNT(DISTINCT product_id)  AS distinct_products  -- distinct non-NULLs only
FROM orders;
-- total_rows=1001, non_null_products=1000, distinct_products=maybe 200

-- Counting distinct (user_id, product_id) combinations — no native syntax:
-- Option 1: subquery dedup then count
SELECT COUNT(*) AS distinct_combos
FROM (
    SELECT DISTINCT user_id, product_id
    FROM orders
    WHERE product_id IS NOT NULL
) sub;

-- Option 2: hash concatenation (less safe — collision risk for short values)
SELECT COUNT(DISTINCT CONCAT(user_id, '-', product_id)) AS distinct_combos
FROM orders;

Performance: Exact vs Approximate

On large tables, exact COUNT(DISTINCT) requires a full scan and an in-memory sort/hash. HyperLogLog approximation runs in constant memory and is parallelisable — acceptable for dashboards where ~1% error is fine.

SQL — exact vs HLL approximate COUNT(DISTINCT), pg_hll, BigQuery

-- Exact COUNT(DISTINCT) on large orders table — slow on millions of rows
SELECT COUNT(DISTINCT user_id) AS exact_unique_users
FROM orders
WHERE created_at >= '2024-01-01';

-- PostgreSQL: pg_hll extension for HyperLogLog
-- Install: CREATE EXTENSION hll;
-- Approximate unique users using HLL sketch:
SELECT hll_cardinality(hll_add_agg(hll_hash_integer(user_id)))::BIGINT
    AS approx_unique_users
FROM orders
WHERE created_at >= '2024-01-01';
-- Typical error: < 1%, runs in a fraction of the time on large datasets.

-- BigQuery: built-in approximate distinct count
SELECT APPROX_COUNT_DISTINCT(user_id) AS approx_unique_users
FROM orders
WHERE created_at >= '2024-01-01';

-- Snowflake: HLL_CARDINALITY or APPROX_COUNT_DISTINCT
SELECT APPROX_COUNT_DISTINCT(user_id) FROM orders;

-- Rule: use exact COUNT(DISTINCT) for billing/compliance;
--       use approximate for analytics dashboards.

Key Points to Remember

1COUNT(DISTINCT col) ignores NULL values — always verify this matches your business intent.
2There is no native COUNT(DISTINCT a, b) syntax; use a subquery with SELECT DISTINCT or hash the combination.
3On large tables, exact COUNT(DISTINCT) requires a full hash-set build — expensive in memory and time.
4HyperLogLog (HLL) approximates cardinality in constant memory with ~0.8–2% error — ideal for analytics.
5PostgreSQL: use the pg_hll extension. BigQuery/Snowflake: APPROX_COUNT_DISTINCT is built in.
6Use exact counting for financial or compliance queries; approximate counting for dashboards and reporting.

Interview Questions

How does COUNT(DISTINCT col) handle NULL values? How is it different from COUNT(*)?

EasyFlipkart

How would you count distinct (user_id, product_id) combinations efficiently in SQL?

MediumAmazon

What is HyperLogLog and when would you use APPROX_COUNT_DISTINCT instead of exact COUNT(DISTINCT)?

HardGoogle

What is the performance bottleneck of COUNT(DISTINCT) on a billion-row table and how do you address it?

HardNetflix

Ask Aria about COUNT(DISTINCT col) & Approximate Counting

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

DISTINCT vs GROUP BY

Scalar Subquery