Duplicate rows in SQL results are one of the most common — and most fixable — problems in data work. This lesson teaches you exactly how DISTINCT, GROUP BY, and COUNT work, when to use each, and how to audit your data for duplicates like a pro.
Imagine you've just pulled a list of customers from your company's database and handed it to the marketing team. They start calling people — and two hours later, someone realizes that half the names appear three times each. The same customer received three calls. Nobody's happy. The data was technically correct; every row in the database was real. But nobody filtered out the duplicates before acting on the results.
This is one of the most common and consequential mistakes in working with databases, and the fix is usually just a few extra characters in your SQL query. In this lesson, you'll learn exactly how duplicates end up in query results, and how to eliminate them using three complementary tools: DISTINCT, GROUP BY, and COUNT. By the end, you'll know not just how to use each approach, but when to reach for each one — and why the difference matters.
What you'll learn:
SELECT DISTINCT to return only unique rowsGROUP BY collapses groups of rows into single summary rowsCOUNT with GROUP BY to see how many duplicates existDISTINCT and GROUP BY, and when to use eachThis lesson assumes you've already written basic SELECT queries and understand filtering with WHERE. If you haven't gotten there yet, start with SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries before continuing here. You don't need any experience with aggregation — we'll cover the relevant parts from scratch.
Before you can fix duplicates, you need to understand where they come from. SQL databases store rows, and a table can absolutely contain multiple rows that look identical — or nearly identical — because the data itself is duplicated, or because you're joining tables in a way that multiplies rows.
Let's use a concrete example. Suppose you work for an e-commerce company. You have an orders table that looks like this:
order_id | customer_email | product_category | order_date
---------|--------------------------|------------------|------------
1001 | alice@example.com | Electronics | 2024-01-15
1002 | bob@example.com | Clothing | 2024-01-16
1003 | alice@example.com | Books | 2024-01-17
1004 | alice@example.com | Electronics | 2024-01-20
1005 | carol@example.com | Electronics | 2024-01-21
1006 | bob@example.com | Electronics | 2024-01-22
This data is perfectly clean. Alice placed three orders; Bob placed two. But watch what happens when someone asks "give me a list of customer emails":
SELECT customer_email
FROM orders;
Result:
customer_email
--------------------------
alice@example.com
bob@example.com
alice@example.com
alice@example.com
carol@example.com
bob@example.com
Six rows, because there are six orders. The question asked for customer emails, but every order has an email, so SQL returned one row per order. This is SQL being completely correct — it has no way of knowing you wanted unique emails unless you tell it.
Key insight
SQL doesn't make assumptions about what you consider "duplicate." By default, every row in the result set is independent. The engine will return one output row per input row (or per combination of joined rows), regardless of whether values repeat. Eliminating duplicates is always your explicit job.
The DISTINCT keyword tells SQL: "Before returning results, throw away any rows that are exact duplicates of a row you've already returned." It sits immediately after SELECT:
SELECT DISTINCT customer_email
FROM orders;
Result:
customer_email
--------------------------
alice@example.com
bob@example.com
carol@example.com
Three rows. Clean list. Ready for the marketing team.
Here's where beginners often get tripped up: DISTINCT doesn't apply to just one column. It applies to the combination of all columns in your SELECT list. This is actually the right behavior, but it surprises people at first.
Watch what happens when you add product_category:
SELECT DISTINCT customer_email, product_category
FROM orders;
Result:
customer_email | product_category
-------------------------|------------------
alice@example.com | Electronics
alice@example.com | Books
bob@example.com | Clothing
bob@example.com | Electronics
carol@example.com | Electronics
Five rows, not three. Alice bought both Electronics and Books, so both combinations are distinct and both appear. This is correct behavior — the query is asking for distinct email + category pairs, not just distinct emails.
Warning
A common mistake is writing SELECT DISTINCT column_a, column_b expecting it to deduplicate only on column_a. It doesn't work that way. DISTINCT considers the whole row. If you need uniqueness on one column while selecting others, you need GROUP BY or a subquery — which we'll cover shortly.
DISTINCT is ideal when:
GROUP BY is more powerful than DISTINCT, and understanding it opens up a much wider range of queries. Rather than simply removing duplicates, GROUP BY collapses multiple rows into a single summary row — one per unique value (or combination of values) in the grouping columns.
Here's the same deduplication, done with GROUP BY:
SELECT customer_email
FROM orders
GROUP BY customer_email;
Result:
customer_email
--------------------------
alice@example.com
bob@example.com
carol@example.com
Identical output to DISTINCT in this case. So why use GROUP BY? Because GROUP BY lets you summarize the data in each group. That's where COUNT comes in.
Note
In simple cases — just returning unique values from one or more columns — SELECT DISTINCT and GROUP BY produce equivalent results. The real power of GROUP BY shows up when you pair it with aggregate functions like COUNT, SUM, or AVG.
Let's say you don't just want a list of unique customers — you want to know how many orders each customer placed. This is one of the most common analytical queries in real-world SQL work.
SELECT customer_email, COUNT(*) AS order_count
FROM orders
GROUP BY customer_email;
Result:
customer_email | order_count
-------------------------|-------------
alice@example.com | 3
bob@example.com | 2
carol@example.com | 1
Here's exactly what SQL is doing, step by step:
orders tablecustomer_email — one bucket per unique emailCOUNT(*)COUNT(*) means "count every row in this group, regardless of what's in any column." It's the most common form. You can also write COUNT(column_name), which counts only rows where that column is not NULL — but COUNT(*) is what you want when counting rows.
Tip
The alias AS order_count gives your counted column a readable name. Without it, the column header in your results might appear as COUNT(*) or just count depending on your database. Always alias your aggregate columns for clarity.
Combine with ORDER BY to find your most active customers:
SELECT customer_email, COUNT(*) AS order_count
FROM orders
GROUP BY customer_email
ORDER BY order_count DESC;
Result:
customer_email | order_count
-------------------------|-------------
alice@example.com | 3
bob@example.com | 2
carol@example.com | 1
For a deeper look at sorting and filtering query results, see Advanced SQL Filtering and Sorting: Mastering WHERE, ORDER BY, and Query Optimization.
You can group by more than one column to analyze combinations. How many orders did each customer place in each product category?
SELECT customer_email, product_category, COUNT(*) AS order_count
FROM orders
GROUP BY customer_email, product_category
ORDER BY customer_email, order_count DESC;
Result:
customer_email | product_category | order_count
-------------------------|------------------|-------------
alice@example.com | Electronics | 2
alice@example.com | Books | 1
bob@example.com | Electronics | 1
bob@example.com | Clothing | 1
carol@example.com | Electronics | 1
Each row represents a unique combination of customer and category. Alice bought Electronics twice, so that row shows 2. Every grouping column must appear in your SELECT list, and every column in your SELECT list that isn't an aggregate function must appear in GROUP BY.
Warning
This last rule is one of the most common SQL errors beginners encounter. If you write SELECT customer_email, product_category, order_date, COUNT(*) FROM orders GROUP BY customer_email, your database will throw an error (or in some systems like MySQL with loose mode, return unpredictable results). The rule: every non-aggregated column in SELECT must be in GROUP BY.
For a comprehensive look at aggregate functions and advanced grouping patterns, check out Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG.
Sometimes you want to count unique values within each group, not just count rows. This is where COUNT(DISTINCT column) comes in — a combination that confuses many beginners but is extremely useful.
Suppose you want to know how many distinct product categories each customer has purchased from:
SELECT customer_email, COUNT(DISTINCT product_category) AS categories_shopped
FROM orders
GROUP BY customer_email;
Result:
customer_email | categories_shopped
-------------------------|-----------------
alice@example.com | 2
bob@example.com | 2
carol@example.com | 1
Alice placed 3 orders but only in 2 distinct categories (Electronics twice, Books once). COUNT(DISTINCT product_category) counts only the unique category values within each group, ignoring the repeat Electronics order.
This pattern is everywhere in analytics — counting distinct users, distinct products, distinct days with activity, and so on.
Now that you've seen both, here's a clear-eyed comparison:
| Situation | Use |
|---|---|
| Just want a list of unique values, no math | SELECT DISTINCT |
| Want to count or summarize each unique group | GROUP BY with COUNT, SUM, etc. |
| Want both unique rows AND a calculation | GROUP BY always |
| Checking "what values exist" in a column | SELECT DISTINCT (simpler, more readable) |
| Filtering based on group size | GROUP BY + HAVING |
Performance-wise, on large datasets, GROUP BY is often more efficient because databases can use their sort and hash aggregation algorithms. DISTINCT behind the scenes often compiles down to a similar operation, but GROUP BY gives the query optimizer more information to work with.
Key insight
Think of DISTINCT as "show me one of each" and GROUP BY as "gather all of each together so I can analyze the pile." When all you need is the list, DISTINCT is cleaner to read. When you need to do any math or filtering on the groups, reach for GROUP BY.
Once you have groups, you'll often want to filter them. Regular WHERE filters rows before grouping. HAVING filters the groups themselves after the grouping and counting is done.
Example: find customers who have placed more than one order:
SELECT customer_email, COUNT(*) AS order_count
FROM orders
GROUP BY customer_email
HAVING COUNT(*) > 1;
Result:
customer_email | order_count
-------------------------|-------------
alice@example.com | 3
bob@example.com | 2
Carol placed only one order and is excluded. You couldn't do this with WHERE, because at the time WHERE runs, the counting hasn't happened yet.
This concept — the order in which SQL clauses execute — is important to internalize. The logical processing order is:
FROM — identify the table(s)WHERE — filter individual rowsGROUP BY — form groupsHAVING — filter groupsSELECT — build the output columnsORDER BY — sort the resultsFor a full exploration of grouping, filtering groups, and aggregation, see Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for Beginners.
Let's put everything together. You've been handed a leads table from a sales system and asked to check data quality. The table has columns: lead_id, email, source, and created_date. You suspect some emails appear multiple times.
Step 1: Confirm the problem exists
SELECT COUNT(*) AS total_rows, COUNT(DISTINCT email) AS unique_emails
FROM leads;
If total_rows is greater than unique_emails, you have duplicates. This is the fastest data quality check you can run.
Step 2: Find which emails are duplicated
SELECT email, COUNT(*) AS occurrences
FROM leads
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;
This surfaces every email that appears more than once, sorted by worst offenders first.
Step 3: Inspect one duplicate to understand why it happened
SELECT *
FROM leads
WHERE email = 'repeat@example.com'
ORDER BY created_date;
Now you can see if it's a true duplicate (identical rows) or the same person submitting from different sources — which might actually be legitimate data worth keeping.
This kind of systematic investigation is what separates someone who can query a database from someone who can analyze one. Understanding the patterns in your duplicates tells you whether you have a data entry problem, a pipeline bug, or a valid data pattern you hadn't considered.
Tip
When auditing for duplicates, always look at the data behind the counts before deleting anything. Two rows with the same email but different source values might represent two different marketing touchpoints — both records could be meaningful.
Use this table definition and sample data to practice. You can run these queries in any SQL environment — PostgreSQL, MySQL, SQLite, or an online sandbox like db-fiddle.com.
CREATE TABLE sales_reps (
rep_id INTEGER,
rep_name TEXT,
region TEXT,
tier TEXT
);
INSERT INTO sales_reps VALUES
(1, 'Jordan Kim', 'Northeast', 'Senior'),
(2, 'Maria Santos', 'Southeast', 'Junior'),
(3, 'Jordan Kim', 'Northeast', 'Senior'),
(4, 'Alex Patel', 'Midwest', 'Senior'),
(5, 'Maria Santos', 'Southeast', 'Senior'),
(6, 'Alex Patel', 'Midwest', 'Senior'),
(7, 'Alex Patel', 'West', 'Senior'),
(8, 'Sam Taylor', 'West', 'Junior');
Exercises:
Write a query using SELECT DISTINCT to return a list of unique rep_name values.
Write a query using GROUP BY to count how many rows exist for each rep_name. Sort by count descending.
Modify your query to show only reps who appear more than once.
Write a query to count how many distinct region values appear in the table.
Write a query to find every distinct rep_name + region combination. Notice how Maria Santos appears twice — once per region she's assigned to, and also has a tier change. How would you query to see each name + tier combination?
Expected answers for exercise 2:
rep_name | row_count
--------------|----------
Alex Patel | 3
Jordan Kim | 2
Maria Santos | 2
Sam Taylor | 1
"My DISTINCT isn't removing rows I thought were duplicates."
Check whether you have extra columns in your SELECT. SELECT DISTINCT first_name, last_name, email will only remove rows where all three match. If emails are duplicated but names differ slightly (typo, middle initial), they'll be treated as distinct.
"I'm getting an error: column X must appear in GROUP BY or aggregate function."
This is the most common GROUP BY error. Every column in your SELECT that isn't wrapped in an aggregate function (COUNT, SUM, MAX, etc.) must be in your GROUP BY clause. Copy every non-aggregated SELECT column into GROUP BY.
"I used WHERE to filter on COUNT but it threw an error."
WHERE cannot reference aggregate functions. Move your condition to HAVING. WHERE COUNT(*) > 5 → error. HAVING COUNT(*) > 5 → correct.
"COUNT(*) and COUNT(column) give me different numbers."
COUNT(*) counts every row. COUNT(column) skips rows where that column is NULL. If your data has NULLs, these will differ. To learn more about how NULL values behave throughout SQL, see NULL Handling in SQL: IS NULL, COALESCE, and NULLIF.
"I need unique values across joined tables and it's not working."
When you join tables, rows can multiply — one-to-many relationships mean each left row matches multiple right rows, and you get one output row per match. DISTINCT or GROUP BY after the join can collapse these, but make sure you understand why the multiplication happened. See SQL JOINs Explained with Real-World Examples for a thorough walkthrough.
You now have three tools for handling duplicate data in SQL:
SELECT DISTINCT — returns unique rows from your result set, operating on the full row (all selected columns together)GROUP BY — collapses multiple rows into groups, enabling aggregation; every non-aggregated column in SELECT must appear in GROUP BYCOUNT (and COUNT(DISTINCT ...))** — counts rows per group, or counts unique values within each groupHAVING — filters groups after aggregation, unlike WHERE which filters rows before groupingThe bigger lesson here is that SQL always returns exactly what you ask for. If you ask for a column, you get a row for every instance of that column — duplicates included. Intentional deduplication is always your explicit responsibility as the query author.
Your next areas to explore: