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