Problem solve Get help with specific problems with your technologies, process and projects.

Counting rows with unique element in a particular column

Is it possible with a single SQL statement to return the count of the number of rows in a table that have a unique element in a particular column?

For example, in a table called tblRegistrants, there is a column called txtName. I would like to be able to get the count of the number of unique or distinct names in the column txtName. I know that I could submit the query then run a loop through the returned cursor to count the number of rows, but in a large data set, this can take longer than I want.

First, let's create a sample table and data:

create table myTable
( Value varchar(30) );

insert into myTable values ('Hello');
insert into myTable values ('World');
insert into myTable values ('Hi');
insert into myTable values ('Earth');
insert into myTable values ('Hello');
In reading this question, I realized there were two ways it could be interpreted. In one interpretation, you could be asking for a count of the distinct entries in a table, in which case I would write SQL like this:
select count(distinct Value) from myTable;
The result would be 4, as Hello, World, Hi, and Earth are the distinct values. However, if it is interpreted that you are wanting a count of those entries that are unique, i.e. those that are found occurring only once, then I would write the SQL like this:
select count(*) 
  from ( select Value from myTable 
           group by Value
           having count(*) = 1 ) UniqueValues;
Here, the result would be 3, as the only values that appear only once are World, Hi, and Earth.

For More Information

This was last published in June 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.