SQL to find the number of distinct values in a column

I can select all the distinct values in a column in the following ways:

• SELECT DISTINCT column_name FROM table_name;
• SELECT column_name FROM table_name GROUP BY column_name;

But how do I get the row count from that query? Is a subquery required?

Solution 1:

You can use the DISTINCT keyword within the COUNT aggregate function:

SELECT COUNT(DISTINCT column_name) AS some_alias FROM table_name

This will count only the distinct values for that column.

Solution 2:

This will give you BOTH the distinct column values and the count of each value. I usually find that I want to know both pieces of information.

SELECT [columnName], count([columnName]) AS CountOf
FROM [tableName]
GROUP BY [columnName]

Solution 3:

Be aware that Count() ignores null values, so if you need to allow for null as its own distinct value you can do something tricky like:

select count(distinct my_col)
       + count(distinct Case when my_col is null then 1 else null end)
from my_table
/

Solution 4:

An sql sum of column_name's unique values and sorted by the frequency:

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name ORDER BY 2 DESC;