How to count unique values in a CSV column

How many customers are in this export? How many different products sold? Which values show up only once? Synth loads your CSV into a SQLite database in your browser, so each of those is one short query. Nothing is installed, and the file never leaves your computer.

The example file

Say you drop a sales.csv like this into Synth. It becomes a table called sales:

order_id | region | product | customer_email
1        | East   | Widget  | ana@example.com
2        | West   | Widget  | Ben@example.com
3        | East   | Gadget  | ben@example.com
4        | east   | Widget  | cy@example.com
5        |        | Gadget  | ana@example.com
6        | North  | Widget  |
7        | West   | Gizmo   | dee@example.com

Count the distinct values

SELECT COUNT(DISTINCT region) AS unique_regions
FROM sales;

This returns 5, which is probably not what you wanted. The blank cell counts as a value, and East and east count separately. The next section fixes both.

Ignore blanks and capitalization

Synth stores an empty cell as empty text. NULLIF(region, '') turns it into NULL, which COUNT(DISTINCT) skips, and lower() makes the comparison case-insensitive:

SELECT COUNT(DISTINCT NULLIF(lower(region), '')) AS unique_regions
FROM sales;

Now the answer is 3: east, west and north. The same pattern counts unique customers by email, where Ben@example.com and ben@example.com are the same person:

SELECT COUNT(DISTINCT lower(customer_email)) AS unique_customers
FROM sales
WHERE customer_email <> '';

That returns 4.

See how often each value appears

A frequency table puts each value on its own row with its count, most common first:

SELECT lower(region) AS region,
       COUNT(*) AS row_count
FROM sales
WHERE region <> ''
GROUP BY lower(region)
ORDER BY row_count DESC;

region | row_count
east   | 3
west   | 2
north  | 1

To add each value's share of all rows, divide by the total:

SELECT region,
       COUNT(*) AS row_count,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM sales), 1) AS pct
FROM sales
GROUP BY region
ORDER BY row_count DESC;

Find values that appear only once

SELECT product, COUNT(*) AS row_count
FROM sales
GROUP BY product
HAVING COUNT(*) = 1;

Here that's Gizmo. Change the condition to HAVING COUNT(*) > 1 to list values that repeat, or see finding duplicate rows to match on whole rows.

Count unique values per group

Put COUNT(DISTINCT) next to a GROUP BY to count within each group, for example how many different products each region sold:

SELECT region, COUNT(DISTINCT product) AS products_sold
FROM sales
WHERE region <> ''
GROUP BY region
ORDER BY products_sold DESC;

Click Download CSV above any result to save it.

Don't want to write SQL?

Sign in and ask the AI assistant something like "how many unique customers are there, ignoring capitalization?" It writes the query from your column names, never your data.

Frequently asked questions