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?

Dunnock

Check my answer https://mcmap.net/q/81790/-counting-values-based-on-distinct-values-from-another-column – Breebreech

Answer 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.

Errolerroll

Neat, i didn't know you could put the distinct keyword there. – Dunnock

also works on groups select A,COUNT(DISTINCT B) from table group by A – Fennec

can you expand this example to distinct on multiple columns? – Greenock

How do you store this some_alias in a new column because I want to use where condition on this some_alias but unable without creating a new column. when I try to add it in a new column it says Error: aggregate functions are not allowed in UPDATE – Hartebeest

Answer 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]

Hamby

Answer 3

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;

Affectional

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
/

Perrone

I really think your case statement was meant to say: case when my_col is null then 1 else my_col end – Expiration

For clarity: SELECT my_col, COUNT(my_col) + COUNT(CASE WHEN my_col IS NULL THEN 1 ELSE NULL END) as CountOf from my_Table GROUP BY my_col – Premium

count(*) includes nulls – Galata

@Galata I guess so, but there's not such a thing as a null row, only a null value, and count(*) is specifically a count of rows. – Perrone

Answer 5

SELECT COUNT(DISTINCT column_name) FROM table as column_name_count;

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

Couldst

Answer 6

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

This will give count of distinct group of columns.

Dewitt

Answer 7

select Count(distinct columnName) as columnNameCount from tableName 

Badajoz

Answer 8

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.

Farman

Answer 9

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

select count(distinct(Column_Name)) from TableName

Fabled

Answer 10

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

Paraffin

Answer 11

You can do this.

Select distinct PRODUCT_NAME_X 
,count (Product_name) products_# 
from TableX
Group by PRODUCT_NAME

It will return

PRODUCT_NAME    products
XXXXXXXXXX      4760

Flagman

Answer 12

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

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

Sliest

Answer 13

Without using DISTINCT this is how we could do it-

SELECT COUNT(C)
FROM (SELECT COUNT(column_name) as C
FROM table_name
GROUP BY column_name)

Dibrin

Answer 14

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]

Dorrie

No, this is not correct. count(field) returns the number of lines where field is not null. – Overhang