Ever wondered how many *unique* things are in a list? Like how many different flavors of ice cream your friends like, even if some voted twice? SQL can help! One of the coolest tools in your SQL toolbox is DISTINCT COUNT. It’s like your digital counting wizard.
TLDR (Too long, didn’t read)
SQL’s COUNT(DISTINCT …) helps you find unique records in a database column. It’s great for identifying how many different customers, products, or values exist. Just remember, it ignores duplicates and highlights only the special snowflakes in your data. Super useful and super easy once you get the hang of it!
What is SQL DISTINCT?
Let’s break it down. Imagine you’re holding a bag of jellybeans. You see:
- Red
- Blue
- Red
- Green
- Blue
How many jellybeans? 5.
How many distinct jellybean colors? 3! (Red, Blue, and Green)
This is what SQL’s DISTINCT does – it removes all the repeats.
How Does COUNT(DISTINCT …) Work?
Let’s say you have a table of customers and the cities they live in. Some cities appear more than once. You want to know how many unique cities there are.
In SQL, you’d write:
SELECT COUNT(DISTINCT city) FROM customers;
Boom. SQL counts all the different cities, but ignores the duplicates.
Why Use DISTINCT COUNT?
It’s super helpful in real life. Here are a few examples:
- Count how many unique visitors came to your website
- See how many different products were sold
- Find out how many departments are in your company
It’s clean, simple, and gives you useful info at a glance.
Let’s Look at a Fun Example!
Here’s a sample table called orders:
| OrderID | Customer | Item |
|---|---|---|
| 1 | Alice | Pizza |
| 2 | Bob | Burger |
| 3 | Alice | Pizza |
| 4 | Charlie | Fries |
Now let’s count:
- Total Customers: 4
- Unique Customers? Use this:
SELECT COUNT(DISTINCT customer) FROM orders;
That gives you 3 – Alice, Bob, and Charlie.
Selecting DISTINCT vs COUNT(DISTINCT)
Sometimes people get confused between:
SELECT DISTINCT city FROM customers;
…and:
SELECT COUNT(DISTINCT city) FROM customers;
Let’s clear it up!
- SELECT DISTINCT: shows you the unique values.
- COUNT(DISTINCT): counts how many unique values are there.
Think of it this way:
- SELECT DISTINCT = “Tell me which ones”
- COUNT(DISTINCT) = “Tell me how many”
Can I Use DISTINCT on Multiple Columns?
Yes! But be careful, it’s a bit more specific.
Say we have a table like this:
| Customer | City |
|---|---|
| Alice | New York |
| Alice | Los Angeles |
| Bob | New York |
| Bob | New York |
If you want to count unique customer-city pairs, use:
SELECT COUNT(DISTINCT customer, city) FROM table_name;
But here’s the kicker: not all SQL servers allow multiple columns inside COUNT(DISTINCT). Some (like MySQL) do, but others (like SQL Server) need a workaround.
In SQL Server, you’d do something like:
SELECT COUNT(*) FROM (
SELECT DISTINCT customer, city FROM table_name
) AS temp;
Same result, just a different road.
Performance Tips
Using COUNT(DISTINCT) is awesome, but it can be slow on big data. Why?
- It has to scan every value
- Then remove duplicates
- Then count what’s left
If your table is huge, consider:
- Adding indexes on the columns you’re counting
- Filtering rows with WHERE to shrink the data
That helps SQL move faster and your queries come back quicker!
Fun Fact 🚀
In some databases, you can use:
SELECT APPROX_COUNT_DISTINCT(customer) FROM table;
It’s faster, but only gives approximate results. Perfect if exact numbers don’t matter much!
Real Life Use Case
Let’s say you run an online store. You want to know:
- How many different customers made purchases last month?
- How many unique products were bought?
- How many cities did you ship to?
All of these can be answered using COUNT(DISTINCT).
SELECT COUNT(DISTINCT customer_id) FROM sales WHERE month = '2024-05';
Look at your data, ask a question, and let DISTINCT count it for you!
Wrap Up
COUNT(DISTINCT …) is one of the friendliest SQL functions out there. It helps you answer big questions with small queries. And the best part? Once you get used to it, you’ll want to use it everywhere!
Keep playing with it. Try adding WHERE, JOIN, or GROUP BY clauses if you’re feeling brave. But remember:
- If you can count jellybeans, you can COUNT DISTINCT!
Now go forth, and count the unique wonders in your database!
