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.

Close