SQL Count Distinct

Galaxy Glossary

How do you count unique values in a SQL table?

The `COUNT DISTINCT` function in SQL is used to count the unique values within a specific column. It's crucial for getting a precise count of distinct items, unlike the standard `COUNT` function which counts all rows.

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

The `COUNT DISTINCT` function is a powerful tool in SQL for obtaining the number of unique values in a particular column. It's often used in data analysis to understand the variety of data present. For example, in a customer database, you might want to know how many different countries your customers are from. `COUNT DISTINCT` is the perfect solution. Unlike the standard `COUNT` function, which counts all rows, `COUNT DISTINCT` only counts each unique value once. This is essential for accurate reporting and analysis, especially when dealing with repeated data entries. For instance, if a product name appears multiple times in an inventory table, `COUNT` would count each occurrence, while `COUNT DISTINCT` would only count the product name once. This function is highly versatile and can be used in conjunction with other SQL functions and clauses to perform more complex calculations and analyses. It's a fundamental building block for data summarization and reporting.

Why SQL Count Distinct is important

The `COUNT DISTINCT` function is vital for accurate data analysis and reporting. It allows you to understand the true variety of data present in a column, avoiding inflated counts due to repeated values. This is crucial for tasks like calculating unique customer locations, product types, or any other situation where you need a precise count of distinct items.

Example Usage


-- Example checking for null values in the 'customer' table
SELECT customer_id, customer_name
FROM customer
WHERE customer_name IS NULL;

-- Example checking for data type mismatch in the 'order_date' column
SELECT order_id, order_date
FROM orders
WHERE order_date NOT LIKE '%/%/%';

-- Example checking for constraint violations (e.g., foreign key)
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Common Mistakes

Forgetting to use `DISTINCT` when you need a unique count, leading to incorrect results.
Using `COUNT` instead of `COUNT DISTINCT`, which will count all occurrences instead of unique ones.
Applying `COUNT DISTINCT` to a column with NULL values; the function will typically exclude NULLs from the count.

SQL Count Distinct

How do you count unique values in a SQL table?

Description

Why SQL Count Distinct is important

Example Usage

Common Mistakes

Want to learn about other SQL terms?

With Cte In SQL

Where Vs Having SQL

Windows Functions In SQL