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
- Dozens more answers to tough SQL questions from Jason Law are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.