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?

12 Answers

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.

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] 

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; 

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 / 

SELECT COUNT(DISTINCT column_name) FROM table as column_name_count; 

you've got to count that distinct col, then give it an alias.

select count(*) from ( SELECT distinct column1,column2,column3,column4 FROM abcd ) T 

This will give count of distinct group of columns.

select Count(distinct columnName) as columnNameCount from tableName 

Using following SQL we can get the distinct column value count in Oracle 11g.

select count(distinct(Column_Name)) from TableName 

After MS SQL Server 2012, you can use window function too.

SELECT column_name, COUNT(column_name) OVER (PARTITION BY column_name) FROM table_name GROUP BY column_name 

To do this in Presto using OVER:

SELECT DISTINCT my_col, count(*) OVER (PARTITION BY my_col ORDER BY my_col) AS num_rows FROM my_tbl 

Using this OVER based approach is of course optional. In the above SQL, I found specifying DISTINCT and ORDER BY to be necessary.

Caution: As per the docs, using GROUP BY may be more efficient.

select count(distinct(column_name)) AS columndatacount from table_name where somecondition=true 

You can use this query, to count different/distinct data.

Count(distinct({fieldname})) is redundant

Simply Count({fieldname}) gives you all the distinct values in that table. It will not (as many presume) just give you the Count of the table [i.e. NOT the same as Count(*) from table]