How to Count Unique Values Per Column

How many unique customers placed orders last quarter? How many distinct products did we sell? How many regions do we operate in? These questions sound simple, but answering them in a spreadsheet usually means writing a COUNTIF formula, creating a helper column, or building a pivot table for something that should take two seconds.

In ExploreMyData, you can get distinct counts instantly from the Column Explorer, or write a SQL query if you need something more specific.

The fast way: Column Explorer

Click any column header to open the Column Explorer. For text and categorical columns, the very first thing you see is the distinct count - the number of unique values in that column.

Below the count is a frequency table showing each unique value and how many times it appears, sorted by frequency. This answers two questions at once: "how many distinct values?" and "what does the distribution look like?"

For a column like "region" with 8 values, the frequency table tells the whole story. For a column like "customer_id" with 12,000 unique values, the count at the top is what matters - you don't need to read the full list.

Numeric columns work too

The Column Explorer for numeric columns focuses on statistics - min, max, mean, median - but you can still see how many distinct values exist. This is useful for spotting columns that look numeric but behave categorically. If a "rating" column only has 5 distinct values (1 through 5), it's effectively a category.

Using SQL for more control

The Column Explorer gives you per-column counts. But sometimes you need to count distinct values with conditions, or count across multiple columns at once. Open the SQL editor and use COUNT(DISTINCT ...).

Count distinct customers:

SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM data

Count distinct values across several columns at once:

SELECT COUNT(DISTINCT customer_id) AS customers, COUNT(DISTINCT product_id) AS products, COUNT(DISTINCT region) AS regions FROM data

Count distinct customers per region:

SELECT region, COUNT(DISTINCT customer_id) AS unique_customers FROM data GROUP BY region ORDER BY unique_customers DESC

Counting unique values with conditions

Often you don't want a global count - you want distinct counts within a specific subset. How many unique products were sold in Q4? How many customers are in the "enterprise" tier?

Add a WHERE clause:

SELECT COUNT(DISTINCT product_id) AS q4_products FROM data WHERE order_date >= '2025-10-01' AND order_date < '2026-01-01'

Or use the visual approach: apply a Filter first to narrow the data to Q4, then open the Column Explorer on the product column. The distinct count now reflects only the filtered rows.

Window functions for running distinct counts

If you need to track how the number of unique customers grows over time, window functions are the way to go. DuckDB supports this natively:

SELECT order_date, COUNT(DISTINCT customer_id) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_customers FROM data

This gives you a running count of unique customers up to each date. It's the kind of query that's painful in a spreadsheet but straightforward in SQL.

When distinct counts reveal problems

Distinct counts aren't just for reporting. They're a data quality signal. If you know your system has 500 products but the column shows 520 distinct values, there are duplicates or variations (typos, inconsistent naming, trailing spaces). If a "country" column has 195 distinct values but you only operate in 12, something slipped through.

Use the Column Explorer's frequency table to find those extra values. Sort by count ascending - the values that appear only once are usually the problems.

Count your unique values →