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

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.