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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.