COUNT(DISTINCT) in SQL: A Complete Guide to Counting Unique Values

In SQL, the COUNT function is used to determine the number of rows in a dataset, making it essential for analyzing data. However, when you need to count only unique values in a column, the DISTINCT keyword comes into play. 

By combining COUNT with DISTINCT, you can efficiently retrieve the number of unique records, such as distinct customers, products, or transactions, instead of counting all rows, including duplicates.

This blog explores Count Distinct in SQL, its syntax, practical examples, performance considerations, and best practices for optimizing queries.

Understanding COUNT Function

The COUNT function in SQL is your go-to tool for determining how many rows exist in a table. Whether you’re analyzing sales, tracking users, or summarizing transactions, COUNT helps you quickly quantify data.

Let’s start with a simple example. Say you have a table called orders, and you want to know how many total records it holds:

SELECT COUNT(*) FROM orders;

This query counts all rows in the orders table, regardless of duplicates or empty values. It’s a straightforward way to get a total record count.

COUNT with WHERE Clause

Now, what if you only want to count specific records? That’s where the WHERE clause comes in. Let’s say you want to count only the orders placed in 2025:

SELECT COUNT(*) FROM orders WHERE order_date >= '2025-01-01';

Here, SQL only counts rows where order_date is in 2025. The WHERE clause filters the data before counting, giving you a precise number based on your conditions.

This ability to filter makes COUNT a powerful tool for analyzing subsets of data—like counting active users, pending transactions, or products in stock.

Optimization Tip: If order_date is indexed, the performance will improve.

Also Read: Top 27 SQL Projects in 2025 With Source Code: For All Levels

Now that you know how COUNT works, what if you only want to count unique values? That’s where DISTINCT helps by filtering out duplicates.

The Role of the DISTINCT Keyword in SQL

When working with data, you’ll often come across duplicate values. Maybe you have multiple orders from the same customer, repeated product entries, or duplicate email addresses in a user database. If you need a list of only unique values, that’s where DISTINCT comes in.

The DISTINCT keyword ensures that your query returns only unique values from a specific column. Let’s say you have a table called customers, and you want to find all the unique cities where your customers are located:

SELECT DISTINCT city FROM customers;

Without DISTINCT, if multiple customers live in the same city, you’d get repeated city names in your result. But by using DISTINCT, SQL removes duplicates and returns only one entry per unique city.

So, when should you use DISTINCT? Here are a few common scenarios:

• Removing Duplicate Customer Entries – If a customer made multiple purchases, but you only need a list of unique customers, DISTINCT ensures each customer appears only once.
• Finding Unique Product Categories – In an e-commerce database, products may belong to various categories. Using DISTINCT helps identify the different categories available.
• Extracting Unique Email Addresses – If a system logs user activity and stores emails repeatedly, DISTINCT helps filter out duplicate entries, giving you a clean email list.
• Analyzing Unique Locations – If you want to see all different cities where your business has customers, DISTINCT ensures each city appears only once in the result.

By eliminating duplicates, DISTINCT helps streamline your data, making your queries more efficient and your reports more accurate.

Also Read: Is SQL Hard to Learn? Challenges, Tips, and Career Insights

You’ve seen how DISTINCT removes duplicates, but what if you need to count those unique values? That’s where COUNT(DISTINCT) comes in.

Combining COUNT with DISTINCT in SQL

Now that you understand both COUNT and DISTINCT, let's put them together. Sometimes, you don’t just want to count all rows—you need to count only unique values. That’s where COUNT(DISTINCT column_name) becomes incredibly useful.

Here’s a simple example of how to count unique values in a column using Count Distinct in SQL:

SELECT COUNT(DISTINCT column_name) FROM table_name;

This query counts the number of distinct (unique) values in a column. Instead of counting all rows, it filters out duplicates before counting.

You’ll use SQL COUNT() with DISTINCT whenever you need to count only unique occurrences. Some common scenarios include:

• Counting Unique Customers – How many different customers placed orders?
• Counting Unique Products Sold – How many different products were purchased?
• Counting Unique Locations – How many cities have sales activity?
• Counting Unique Employee Departments – How many different departments exist in a company?

Also Read: How to Use SQL for Data Science [Benefits, Skills and Career Opportunities]

Let’s go through two practical examples using Count Distinct in SQL.

Practical Example 1: Using COUNT(DISTINCT) for Fraud Detection in Login Attempts

In an e-commerce platform, fraud detection is critical. One of the ways to identify potential fraudulent behavior is by tracking how many unique IP addresses are used in login attempts for a specific user. 

If a user suddenly logs in from multiple IP addresses in a short period of time, it may indicate suspicious behavior, such as account takeover or unauthorized access attempts.

Scenario: You want to track how many unique IP addresses have been used by each user during their login attempts to identify suspicious activity.

Here's the schema for the login_attempts table:

CREATE TABLE login_attempts (
    attempt_id INT PRIMARY KEY,
    user_id INT,
    ip_address VARCHAR(45),
    attempt_time TIMESTAMP
);

Sample data:

INSERT INTO login_attempts (attempt_id, user_id, ip_address, attempt_time) VALUES
(1, 101, '192.168.1.1', '2024-02-01 08:00:00'),
(2, 101, '192.168.1.2', '2024-02-01 09:00:00'),
(3, 101, '192.168.1.1', '2024-02-01 10:00:00'),
(4, 102, '192.168.1.3', '2024-02-01 11:00:00'),
(5, 102, '192.168.1.3', '2024-02-01 12:00:00'),
(6, 103, '192.168.1.4', '2024-02-01 13:00:00'),
(7, 103, '192.168.1.5', '2024-02-01 14:00:00'),
(8, 103, '192.168.1.5', '2024-02-01 15:00:00');

To identify the number of unique IP addresses used by each user, you can use the COUNT(DISTINCT ip_address) function:

SELECT user_id, COUNT(DISTINCT ip_address) AS unique_ips
FROM login_attempts
GROUP BY user_id;

Expected Output:

Explanation:

• User 101 has logged in from two unique IP addresses (192.168.1.1 and 192.168.1.2).
• User 102 has only used one IP address (192.168.1.3).
• User 103 has logged in from two unique IP addresses (192.168.1.4 and 192.168.1.5).

This query is useful because it allows you to identify if a user is attempting to log in from multiple locations (potentially indicating account hijacking). 

For example, if user 101 is known to only access the account from a specific region, but logs in from two different IP addresses across distinct geographic locations, you may flag it for further investigation.

Why This is Useful in Fraud Detection:

• Suspicious Activity: Multiple unique IP addresses within a short time frame could indicate that an account is being accessed from unusual locations, suggesting potential fraud or a compromised account.
• Risk Mitigation: By monitoring the number of unique IPs, you can implement strategies such as blocking suspicious IPs or requiring additional verification steps (e.g., multi-factor authentication) if the user is logging in from many different locations.
• Improved User Behavior Analytics: Tracking IPs helps in understanding typical user behavior patterns, so deviations can be flagged in real-time.

Using COUNT(DISTINCT) in this way helps build a reliable system for detecting and preventing unauthorized access or fraudulent activity in real-time.

Practical Example 2: Using COUNT(DISTINCT) for E-commerce Analytics (Counting Unique Users Who Added Items to Cart)

In e-commerce, it's crucial to track customer behavior to understand which products are getting attention and how many different users are interacting with those products. 

One important metric for understanding interest in products is the number of unique users who added an item to their shopping cart.

Scenario: You want to track how many different users have added a specific product to their cart. This can help determine how popular a product is and whether it has the potential to convert into sales.

Here’s the schema for the cart_additions table:

CREATE TABLE cart_additions (
    addition_id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    addition_date DATE
);

Sample data:

INSERT INTO cart_additions (addition_id, user_id, product_id, addition_date) VALUES
(1, 101, 201, '2024-02-01'),
(2, 102, 202, '2024-02-01'),
(3, 101, 203, '2024-02-01'),
(4, 103, 201, '2024-02-02'),
(5, 104, 204, '2024-02-02'),
(6, 101, 202, '2024-02-03'),
(7, 105, 201, '2024-02-03'),
(8, 106, 202, '2024-02-04');

Query to Count Unique Users Who Added a Specific Product to Cart: Suppose you want to know how many unique users added product 201 to their cart. You can use COUNT(DISTINCT) to ensure you don't double-count users who added the product multiple times:

SELECT COUNT(DISTINCT user_id) AS unique_users_added_product_201
FROM cart_additions
WHERE product_id = 201;

Expected Output:

Explanation:

• Product 201 has been added to the cart by users 101, 103, 105, and 107.
• Even though product 201 was added multiple times (user 101 added it twice), COUNT(DISTINCT) only counts the unique users who added it to their cart, so the total count is 4.

Why This Is Useful in E-Commerce Analytics:

• Product Popularity: By tracking unique users who add a product to their cart, you can measure the level of interest in a product. A higher number of unique users indicates strong demand.
• Conversion Potential: If a product has many unique users adding it to their cart but low sales conversion, it might indicate a problem with checkout or payment processes, or it could signal issues with pricing or shipping.
• Customer Segmentation: By knowing how many unique users are engaging with certain products, you can segment your customer base more effectively for targeted marketing campaigns or special offers.

For example, if you notice a high number of users adding product 201 to their cart but not completing the purchase, you might want to run an analysis on potential drop-off points or even send out a reminder email or offer a discount to encourage those users to finalize their purchase.

Extended Use Case: Comparing Product Interest

You could extend this query to compare how many unique users added multiple products to their cart, helping you understand which products are being viewed the most.

SELECT product_id, COUNT(DISTINCT user_id) AS unique_users_added
FROM cart_additions
GROUP BY product_id
ORDER BY unique_users_added DESC;

Expected Output:

This shows which products are attracting the most attention and which might require more visibility to increase user interaction.

Why This is Valuable:

• Marketing Focus: Products with higher unique user engagement are likely the ones that deserve more marketing resources.
• Inventory Planning: Tracking which products are added frequently helps predict demand and optimize inventory levels.

This technique is essential for making data-driven decisions in business analysis, inventory tracking, and customer segmentation.

Also Read: Stored Procedure in SQL: How to Create, Executive, Modify, Types & Use Cases

Advanced Scenarios Using COUNT(DISTINCT) in SQL

The COUNT(DISTINCT column_name) function becomes even more powerful when used with GROUP BY, NULL values, and JOINs. These advanced scenarios help analyze unique data across different dimensions efficiently.

1. COUNT(DISTINCT) with GROUP BY

Using COUNT(DISTINCT) with GROUP BY helps count unique values within each category. This is useful when analyzing data across different segments, like finding unique customers per region.

Example: Counting Unique Customers Per Region

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    region VARCHAR(50)
);

INSERT INTO orders (order_id, customer_id, region) VALUES
(1, 101, 'North'),
(2, 102, 'South'),
(3, 103, 'North'),
(4, 101, 'North'),
(5, 104, 'East'),
(6, 102, 'South'),
(7, 105, 'West'),
(8, 101, 'North');

SELECT region, COUNT(DISTINCT customer_id) AS unique_customers 
FROM orders 
GROUP BY region;

Expected Output:

Explanation:

• GROUP BY region groups the data by region.
• COUNT(DISTINCT customer_id) ensures that duplicate customers in each region are counted only once.
• Customer 101 appears three times in the North region but is counted once.

This helps businesses understand customer distribution across regions.

2. Handling NULL Values with COUNT(DISTINCT)

By default, COUNT(DISTINCT) ignores NULL values, meaning any missing data won’t be included in the count.

Example: Counting Unique Email Addresses

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100)
);

INSERT INTO users (user_id, email) VALUES
(1, 'alice@email.com'),
(2, 'bob@email.com'),
(3, NULL),
(4, 'alice@email.com');

SELECT COUNT(DISTINCT email) AS unique_emails FROM users;

Expected Output:

Unique_emails
2

Explanation:

• The COUNT(DISTINCT email) removes duplicates (alice@email.com appears twice).
• NULL values are ignored in the count.
• Only two unique email addresses remain: alice@email.com and bob@email.com.

If you want to count NULLs as a unique value, use COALESCE() to replace NULLs with a placeholder:

SELECT COUNT(DISTINCT COALESCE(email, 'Unknown')) FROM users;

It ensures that NULL values are counted as a unique entry. This is useful for email marketing lists to ensure unique contacts are counted.

3. Using COUNT(DISTINCT) with JOINs

When working with multiple tables, SQL COUNT() with DISTINCT ensures unique values are counted across different datasets.

Example: Counting Unique Products Sold Per Customer

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    membership_status VARCHAR(20)
);

INSERT INTO customers (customer_id, name, membership_status) VALUES
(101, 'Alice', 'Premium'),
(102, 'Bob', 'Standard'),
(103, 'Charlie', 'Premium');

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO orders (order_id, customer_id, product_id) VALUES
(1, 101, 201),
(2, 101, 202),
(3, 102, 201),
(4, 101, 201),
(5, 103, 203),
(6, 103, 204),
(7, 103, 203);

SELECT COUNT(DISTINCT o.product_id) AS unique_products_sold 
FROM orders o 
JOIN customers c ON o.customer_id = c.customer_id 
WHERE c.membership_status = 'Premium';

Expected Output:

unique_products_sold
3

Explanation:

• The JOIN links orders to customers, filtering only those with 'Premium' membership.
• COUNT(DISTINCT o.product_id) ensures we count only unique products purchased by premium customers.
• Even though customer 103 bought product 203 twice, it is counted only once.

Using these techniques will make your SQL queries more powerful and help in efficient data analysis.

Potential Issue: If customers contains duplicate customer_id, it may overcount.

To avoid overcounting due to duplicate customer_id entries in the customers table, use a subquery with DISTINCT to select only unique customer_id values before joining with the orders table. This ensures each customer is counted once, even if there are multiple records for the same customer.

Also Read: SQL Vs MySQL: Difference Between SQL and MySQL

COUNT(DISTINCT) is useful, but it can slow down queries on large datasets. Let’s explore how you can optimize performance.

Performance Considerations for COUNT(DISTINCT)

While COUNT(DISTINCT column_name) is a powerful function for counting unique values, it can slow down queries on large datasets. Understanding how SQL processes this query and applying optimizations can significantly improve performance.

The COUNT(DISTINCT) function requires SQL to:

• Scan the entire column to collect values.
• Eliminate duplicates, which can be resource-intensive.
• Count the remaining unique values.

On large tables, this can cause delays, especially if the column being counted is not indexed. The performance impact worsens if the table contains millions of records or if multiple COUNT(DISTINCT) queries run simultaneously.

Here are a few ways to optimize queries using Count Distinct in SQL:

• Index the Column: Creating an index on the column being counted helps SQL retrieve unique values faster.

 CREATE INDEX idx_customer_id ON orders(customer_id);

• Use WHERE to Reduce Data Size: Filtering unnecessary rows before counting improves efficiency.

 SELECT COUNT(DISTINCT customer_id) FROM orders WHERE order_date >= '2024-01-01';

• Partition Large Tables: For huge datasets, partitioning (e.g., by date) reduces query load and improves performance.

PARTITION BY RANGE(order_date);

You can also choose alternative approaches:

• Using GROUP BY Instead: If you need counts per category, GROUP BY is often more efficient than COUNT(DISTINCT).

 SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;

• Using DISTINCT Alone: If you only need unique values (not the count), using DISTINCT alone is faster.

 SELECT DISTINCT customer_id FROM orders;

Optimizing SQL COUNT() with DISTINCT ensures faster queries, especially when handling big data or high-traffic databases. 

Also Read: SQL vs PL SQL: Difference Between SQL & PL/SQL

Common Errors and Troubleshooting in COUNT(DISTINCT) Queries

While SQL COUNT() with DISTINCT is a powerful tool for counting unique values, it can lead to unexpected results or performance problems if not used carefully. These issues typically arise due to incorrect syntax, NULL handling, inefficient indexing, or unintended duplications from JOINs.

Below is a table highlighting common mistakes in COUNT(DISTINCT) queries, explanations of what goes wrong, and the solutions to fix them.

By following these troubleshooting steps, you can improve query accuracy and performance when using COUNT(DISTINCT). 

Also Read: SQL Interview Questions and Answers for Beginners and Experienced Professionals

Mastering SQL requires both theory and practical experience. If you want to sharpen your skills, upGrad offers expert-led courses to help you excel.

How Can upGrad Help You Master COUNT(DISTINCT) in SQL?

upGrad, South Asia’s leading EdTech platform, offers specialized courses in SQL and database management that cover essential skills for efficient data handling. These courses provide a comprehensive curriculum, including foundational SQL concepts, advanced querying techniques, and performance optimization strategies.

With 10M+ learners trained, upGrad equips individuals with technical expertise, hands-on projects, and real-world applications, ensuring a strong grasp of database management and SQL analytics.

Here are some relevant courses to enhance your SQL learning journey:

• Master of Design in User Experience
• Advanced SQL: Functions and Formulas
• Case Study using Tableau, Python and SQL
• Introduction to Database Design with MySQL
• AI-Powered Full Stack Development Course by IIITB
• Professional Certificate Program in UI/UX Design & Design Thinking

You can also get personalized career counseling with upGrad to guide your career path, or visit your nearest upGrad center and start hands-on training today! 

Boost your career with our popular Software Engineering courses, offering hands-on training and expert guidance to turn you into a skilled software developer.

Explore our Popular Software Engineering Courses

Master in-demand Software Development skills like coding, system design, DevOps, and agile methodologies to excel in today’s competitive tech industry.

In-Demand Software Development Skills

Stay informed with our widely-read Software Development articles, covering everything from coding techniques to the latest advancements in software engineering.

Read our Popular Articles related to Software