Q

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 first published in October 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close