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

DISTINCT with GROUP BY

Could you give us a situation where both DISTINCT and GROUP BY are required?

Could you give us a situation where both DISTINCT and GROUP BY are required?

Imagine a village of many families, each consisting of family members. Someone with no relatives whatsoever is a family of one. Now let us take a survey of the population, and ask, "What are the different family sizes in this village?"

create table families
( familyid tinyint 
, person varchar(9)
);
insert into families values
 (1,'curly')
,(1,'larry')
,(1,'moe')
,(1,'shemp')
,(1,'joe')
,(1,'curly joe')
,(2,'fred')
,(3,'tom')
,(3,'dick')
,(3,'harry')
,(4,'huey')
,(4,'dewie')
,(4,'louie')
;
select distinct count(*) as familysize
  from families
group by familyid

The result set is 6, 1, 3. Without DISTINCT, it would be 6, 1, 3, 3. Add a few hundred more families of size 3 and you will notice the difference.

This was last published in October 2005

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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close