Count distinct SQL: Unique Value Counting

In every corner of every organization, data is constantly being collected, analyzed, and acted upon. From user registrations and product SKUs to email addresses and transaction IDs, it’s essential to answer a deceptively simple question: How many unique values are there? This is where COUNT DISTINCT in SQL becomes a powerful tool. Whether you’re building dashboards or cleaning datasets, understanding how to count unique values in SQL can make your queries smarter, faster, and more insightful.

TL;DR (Too Long, Didn’t Read)

COUNT DISTINCT in SQL is used to determine the number of unique, non-null values in a column. It’s ideal for summarizing data without duplicates and can be combined with GROUP BY and other SQL clauses to build complex analyses. While it’s simple on the surface, nuances like NULL handling, optimization for performance, and multi-column distinct counts can make a big difference in results. By mastering COUNT DISTINCT, you’ll bring more accuracy and depth to your SQL queries.

What Is COUNT DISTINCT?

In SQL, COUNT is a commonly used aggregate function to count the number of rows in a table. But sometimes, we don’t want to count all rows—we want to know how many distinctly different values appear in a column. Enter COUNT(DISTINCT column_name), which returns the number of unique non-null entries in that column.

SELECT COUNT(DISTINCT customer_id) 
FROM orders;

The above query tells you how many unique customers have placed an order. Any duplicate customer_id values or NULLs are ignored, giving you a cleaner view of genuine count diversity.

Why Use COUNT DISTINCT?

There are a number of practical benefits to using COUNT DISTINCT:

• Summarize Unique Data: Understand the number of different products sold, users registered, or emails collected.
• Data Integrity Checks: Verify if there are duplicate entries in fields that should be unique, like email addresses.
• Cleaning Data: Quickly identify fragmented records or inconsistent value entries.

For instance, if you’re working in marketing, you might want to assess how many unique visitors accessed your campaign. If you’re in retail, you might analyze how many individual stock-keeping units (SKU) were sold over a period.

The Impact of NULL Values

It’s important to remember that COUNT(DISTINCT column_name) will ignore NULL values. This has real implications for your data analysis. Consider the following table:

Order ID	Customer Email
1	alice@example.com
2	bob@example.com
3	alice@example.com
4	NULL
5	charlie@example.com

Running this query:

SELECT COUNT(DISTINCT customer_email) FROM orders;

Would return 3 (not 4), because one value is NULL and duplicates are filtered out. If you want to count even NULLs as unique values, you’ll need a separate technique involving CASE statements or additional fields.

Multi-Column COUNT DISTINCT

There are times you need to count unique combinations of values across multiple columns. For example, if you want to know how many different user and product combinations exist in a transaction table, SQL allows:

SELECT COUNT(DISTINCT user_id, product_id) 
FROM transactions;

But beware—not all SQL engines support multi-column DISTINCT like this syntax. In databases like PostgreSQL and MySQL (v8+), the above syntax works. However, for engines that don’t, you’ll need to create a derived column. For example:

SELECT COUNT(*) 
FROM (
  SELECT DISTINCT user_id, product_id 
  FROM transactions
) AS unique_combos;

Using COUNT DISTINCT with GROUP BY

Combining GROUP BY with COUNT DISTINCT allows you to segment your uniqueness analysis by categories. Suppose you want to know how many unique users purchased something each day:

SELECT 
  purchase_date, 
  COUNT(DISTINCT user_id) AS unique_users
FROM sales
GROUP BY purchase_date
ORDER BY purchase_date;

This query enables trend insights over time—great for analytics and reports. The pivot between raw data and meaningful investigation often begins with this tool.

Performance Tips for Large Datasets

While COUNT DISTINCT is powerful, it can get expensive for large tables. Unique value counting usually requires creating a new hash set or memory index to filter out duplicates, which takes time and computational resources.

Here are some tips to optimize performance:

• Use Indexing: Ensure the column(s) you’re querying have indexes, especially if you’re using WHERE filters.
• Filter Early: Apply WHERE clauses first; the fewer rows passed to the COUNT function, the better.
• Consider Approximations: Engines like BigQuery offer APPROX_COUNT_DISTINCT for near-real results with dramatically improved speed.
• Deduplicate with CTEs: For complex queries, consider creating a Common Table Expression (CTE) to pre-filter your data.

Example of using a CTE:

WITH unique_users AS (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
)
SELECT COUNT(*) FROM unique_users;

Alternatives and Advanced Methods

Though COUNT DISTINCT is a direct tool for unique counting, depending on your SQL engine, there may be other methods better suited for different goals.

Some useful alternatives include:

• Window Functions: Use ROW_NUMBER() or RANK() to identify duplicates or unique entries directly.
• Subqueries: You can build derived tables to manage complex joins and filters before applying COUNT.
• Hashes or Encoded Strings: In some ETL processes, combining multiple columns into a single hashed string can make unique counting more efficient.

Also, explore database-specific features:

• BigQuery: APPROX_COUNT_DISTINCT()
• PostgreSQL: COUNT(DISTINCT x, y) or with UUIDs
• Oracle: Using SELECT DISTINCT wrapped inside aggregated query levels

Real-Life Use Cases

Understanding the number of unique values is key to real-world applications:

• Counting how many distinct users sign into a mobile app each week
• Finding how many unique email addresses are signed to a newsletter
• Determining how many different cities orders are being shipped to
• Checking for possible duplication in a national ID or phone number field

In each scenario, counting distinct values leads directly to insights, be it detecting fraud, measuring growth, or tracking engagement.

Conclusion

COUNT DISTINCT is one of those SQL tools that feels basic but is immensely powerful when used well. It gives you clarity in your data by stripping away repetition and focusing on what truly matters: uniqueness. From determining customer bases to preventing data corruption, it’s an indispensable part of working with relational databases.

So the next time someone asks you, “How many unique X do we have?”, you’ll smile and fire off the perfect query with COUNT DISTINCT.