SQL to find the number of distinct values in a column

Question

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?

Answer 1

select Count(distinct columnName) as columnNameCount from tableName

Answer 2

You should be able to use this:

SELECT COUNT(DISTINCT column_name) AS some_alias FROM table_name

This will count only the distinct values for that column.

Answer 3

SELECT COUNT(DISTINCT column_name) FROM table as column_name_count;

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

Answer 4

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
/

Answer 5

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

This will give count of distinct group of columns.