SQL Academy · Lesson

Counting Unique Values

COUNT(DISTINCT ...) in practice.

Counting Unique Values is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Count Unique Values?

When you use COUNT(*), you count every row — including duplicates. But sometimes you only want to know how many distinct values exist in a column.

For example: how many different countries do your customers come from? How many unique products were ordered last month? This is where COUNT(DISTINCT ...) comes in.

Setting Up a Sample Table

Let's create an orders table to practice with. Each order has a customer name, a product, and a country.

Notice that some customers and countries appear more than once — that's intentional, so we can see the difference between counting all rows and counting distinct values.

CREATE TABLE orders (
  id INT,
  customer VARCHAR(50),
  product VARCHAR(50),
  country VARCHAR(50)
);

INSERT INTO orders VALUES
  (1, 'Alice', 'Laptop', 'USA'),
  (2, 'Bob', 'Phone', 'UK'),
  (3, 'Alice', 'Tablet', 'USA'),
  (4, 'Carol', 'Laptop', 'Canada'),
  (5, 'Bob', 'Laptop', 'UK'),
  (6, 'Dave', 'Phone', 'USA'),
  (7, 'Carol', 'Phone', 'Canada'),
  (8, 'Eve', 'Tablet', 'Germany');

COUNT(*) vs COUNT(DISTINCT)

Run both queries and compare the results. COUNT(*) returns 8 — one for each row. COUNT(DISTINCT customer) returns 5 — because there are only 5 unique customers (Alice, Bob, Carol, Dave, Eve).

This is the core idea: COUNT(DISTINCT column) ignores repeated values and counts only unique ones.

SELECT COUNT(*) AS total_orders,
       COUNT(DISTINCT customer) AS unique_customers
FROM orders;

Counting Distinct Countries

Let's find out how many different countries our customers come from. Even though the orders table has 8 rows, several rows share the same country.

The result will be 4 — USA, UK, Canada, and Germany — no matter how many times each appears.

SELECT COUNT(DISTINCT country) AS unique_countries
FROM orders;

Counting Distinct Products

We can apply the same pattern to any column. Here we count how many different products have been ordered.

Even though 'Laptop' appears three times and 'Phone' appears three times, each product is counted only once. The answer is 3: Laptop, Phone, Tablet.

SELECT COUNT(DISTINCT product) AS unique_products
FROM orders;

Using COUNT(DISTINCT) with WHERE

You can combine COUNT(DISTINCT ...) with a WHERE clause to narrow down the rows before counting. Here we count distinct products ordered only by customers from the USA.

Only rows where country = 'USA' are considered, and then we count unique products among those rows.

SELECT COUNT(DISTINCT product) AS usa_unique_products
FROM orders
WHERE country = 'USA';

COUNT(DISTINCT) with GROUP BY

When paired with GROUP BY, COUNT(DISTINCT ...) gives you distinct counts per group. This query answers: for each country, how many unique customers placed orders?

Each group is processed independently, so duplicates within a group are removed before counting.

SELECT country,
       COUNT(DISTINCT customer) AS unique_customers
FROM orders
GROUP BY country
ORDER BY unique_customers DESC;

Counting Distinct Values in Multiple Columns

You can use several COUNT(DISTINCT ...) expressions in the same SELECT. This gives you a quick overview of cardinality — how many unique values each column holds — all in one query.

Cardinality is a useful metric when exploring a new dataset.

SELECT COUNT(DISTINCT customer) AS unique_customers,
       COUNT(DISTINCT product)  AS unique_products,
       COUNT(DISTINCT country)  AS unique_countries
FROM orders;

How NULL Is Treated

Just like regular COUNT(column), COUNT(DISTINCT column) ignores NULL values. A NULL is not counted as a unique value.

In this example, two rows have a NULL country. The result is still 4 distinct non-null countries, not 5.

INSERT INTO orders VALUES
  (9,  'Frank', 'Phone',  NULL),
  (10, 'Grace', 'Laptop', NULL);

SELECT COUNT(DISTINCT country) AS unique_countries_no_null
FROM orders;

Comparing COUNT(*), COUNT(col), and COUNT(DISTINCT col)

It is helpful to see all three COUNT forms side by side:

COUNT(*) — counts every row including NULLs
COUNT(country) — counts rows where country is not NULL
COUNT(DISTINCT country) — counts unique non-NULL country values

Run this on the updated table (now 10 rows, 2 with NULL country) to confirm the differences.

SELECT COUNT(*)                AS total_rows,
       COUNT(country)          AS non_null_countries,
       COUNT(DISTINCT country) AS unique_countries
FROM orders;

Real-World Use Case: Daily Active Users

One of the most common uses of COUNT(DISTINCT ...) in analytics is counting Daily Active Users (DAU). You have a log of events and want to know how many unique users were active each day.

GROUP BY the date and COUNT(DISTINCT user_id) to get a distinct user count per day — duplicates from the same user clicking multiple times are automatically excluded.

CREATE TABLE user_events (
  event_date DATE,
  user_id    INT
);

INSERT INTO user_events VALUES
  ('2024-01-01', 1), ('2024-01-01', 2), ('2024-01-01', 1),
  ('2024-01-02', 2), ('2024-01-02', 3), ('2024-01-02', 2),
  ('2024-01-03', 1), ('2024-01-03', 4);

SELECT event_date,
       COUNT(DISTINCT user_id) AS daily_active_users
FROM user_events
GROUP BY event_date
ORDER BY event_date;

Quick Check

Test your understanding of COUNT(DISTINCT ...) with this question.

Lesson Recap

Great work! Here is a summary of what you learned about COUNT(DISTINCT ...):

COUNT(DISTINCT column) counts only unique, non-NULL values in a column.
It differs from COUNT(*) (all rows) and COUNT(column) (non-NULL rows).
It works with WHERE to filter rows before counting.
It works with GROUP BY to count distinct values per group.
NULL values are always excluded from the distinct count.
Multiple COUNT(DISTINCT ...) expressions can appear in the same SELECT.
Common real-world uses include counting unique users, products, countries, and sessions.

Mastering this function gives you a powerful tool for data exploration and analytics.

Frequently asked questions

Is the “Counting Unique Values” lesson free?

Yes — the full text of “Counting Unique Values” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Counting Unique Values”?

COUNT(DISTINCT ...) in practice. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Counting Unique Values” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy