By Christian Oudard


2008-09-26 19:52:48 8 Comments

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?

9 comments

@Nilesh Shinde 2018-12-26 02:07:16

**

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

**

Select count(distinct(Column_Name)) from TableName

@Wayne 2008-09-26 19:54:47

select Count(distinct columnName) as columnNameCount from tableName 

@Vaibhav 2008-11-04 09:37:48

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

This will give count of distinct group of columns.

@xchiltonx 2015-05-20 00:04:51

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;

@Paul Pena 2014-12-18 02:13:16

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]

@Antti29 2015-03-26 07:22:47

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

@Noah Goodrich 2008-09-26 19:54:59

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.

@Christian Oudard 2008-09-26 19:55:57

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

@tmanthey 2013-04-18 15:07:41

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

@eugene 2015-03-05 06:41:27

can you expand this example to distinct on multiple columns?

@Jacky Supit 2018-06-09 01:30:00

dude!! thank you, saved my day.

@Paul James 2012-10-27 15:45:30

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 distinct columnName, count(columnName) as CountOf from tableName group by columnName

@Antti29 2014-01-31 12:35:50

Since you're grouping by columnName, you're already getting the distinct values just once and the distinct keyword doesn't do anything here. Try the query without it, the resultset is exactly the same.

@David Aldridge 2008-09-26 21:32:04

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
/

@James Jensen 2015-03-11 18:19:56

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

@Michael Paulukonis 2015-06-18 17:59:25

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 2017-07-12 17:05:27

count(*) includes nulls

@David Aldridge 2018-01-06 14:50:59

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

@Pete Karl II 2008-09-26 19:55:38

SELECT COUNT(DISTINCT column_name) FROM table as column_name_count;

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

Related Questions

Sponsored Content

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

7 Answered Questions

[SOLVED] SQL Server query - Selecting COUNT(*) with DISTINCT

14 Answered Questions

[SOLVED] How do I limit the number of rows returned by an Oracle query after ordering?

25 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2291484 View
  • 1551 Score
  • 25 Answer
  • Tags:   sql duplicates

19 Answered Questions

[SOLVED] LINQ's Distinct() on a particular property

10 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

37 Answered Questions

6 Answered Questions

[SOLVED] SELECT DISTINCT on one column

17 Answered Questions

Sponsored Content