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?
-
Check my answer stackoverflow.com/a/75068192/8614314Palash Mondal– Palash Mondal2023-01-10 09:54:31 +00:00Commented Jan 10, 2023 at 9:54
Add a comment
|
14 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.
4 Comments
Christian Oudard
Neat, i didn't know you could put the distinct keyword there.
tmanthey
also works on groups
select A,COUNT(DISTINCT B) from table group by A
eugene
can you expand this example to distinct on multiple columns?
Sreenivas Murali
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
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]
Comments
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;
Comments
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
/
4 Comments
James Jensen
I really think your case statement was meant to say:
case when my_col is null then 1 else my_col end
Michael Paulukonis
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
PragmaticProgrammer
count(*) includes nulls
David Aldridge
@PragmaticProgrammer 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.SELECT COUNT(DISTINCT column_name) FROM table as column_name_count;
you've got to count that distinct col, then give it an alias.
Comments
select count(*) from
(
SELECT distinct column1,column2,column3,column4 FROM abcd
) T
This will give count of distinct group of columns.
Comments
select Count(distinct columnName) as columnNameCount from tableName
Comments
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.
Comments
Using following SQL we can get the distinct column value count in Oracle 11g.
select count(distinct(Column_Name)) from TableName
Comments
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
Comments
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
Comments
select count(distinct(column_name)) AS columndatacount from table_name where somecondition=true
You can use this query, to count different/distinct data.
Comments
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)
Comments
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]
1 Comment
Antti29
No, this is not correct.
count(field) returns the number of lines where field is not null.