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?

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]

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
/

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;

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]