How to Count Unique Values in SQL?
Count Unique Values in SQL is a SQL query that this sql statement counts the number of distinct values in a specified column from a table.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Counting unique values in a column is essential for data analysis. Use COUNT(DISTINCT column_name) to easily achieve this.
The Formula
"Count the number of unique values in a column using COUNT(DISTINCT column_name)"
SELECT COUNT(DISTINCT column_name) FROM table_name;This SQL statement counts the number of distinct values in a specified column from a table.
Step-by-Step Breakdown
- SELECT indicates the columns to be returned.
- COUNT is an aggregate function that counts the number of rows.
- DISTINCT ensures that only unique values are counted.
- column_name specifies the column from which to count unique values.
- FROM indicates the table from which to retrieve the data.
Edge Cases & Warnings
- The column contains NULL values, which are not counted as unique.
- All values in the column are the same, resulting in a count of 1.
- The column is empty, which results in a count of 0.
- Using COUNT(DISTINCT) on a large dataset may impact performance.
- The column has non-unique values but includes duplicates.
Examples
"SELECT COUNT(DISTINCT city) FROM customers;"
5 (if there are 5 unique cities)"SELECT COUNT(DISTINCT product_id) FROM orders;"
10 (if there are 10 unique product IDs)Frequently Asked Questions
What does COUNT(DISTINCT) do?
It counts the number of unique values in a specified column.
Can COUNT(DISTINCT) count NULL values?
No, NULL values are not counted as unique.
Is COUNT(DISTINCT) performance-intensive?
Yes, it can be slow on large datasets due to the need to identify unique values.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.