Counting and Grouping with DISTINCT vs GROUP BY: When to Use Each and Why It Matters

Introduction

Imagine you're a data analyst at a SaaS company. Your manager asks: "How many unique customers placed an order last month?" You write a quick query, get back a number, and pass it along. But then the marketing team runs the same question and gets a different answer. Who's right?

The culprit is almost always a misunderstanding between two SQL tools that look like they do the same thing: DISTINCT and GROUP BY. Both can remove duplicates. Both can count unique values. But they approach the problem from completely different angles — and choosing the wrong one doesn't just give you ugly code, it can silently give you wrong answers.

By the end of this lesson, you'll know exactly what each one does under the hood, when to reach for one over the other, and how to combine them in ways that make your queries both correct and easy to read. This is the kind of knowledge that separates analysts who get the right number the first time from those who spend an hour debugging a query that "should have worked."

What you'll learn:

• How DISTINCT works and when it's the right tool
• How GROUP BY works and why it's fundamentally different from DISTINCT
• How COUNT(DISTINCT ...) bridges the two concepts
• Common traps that lead to inflated or deflated counts
• Practical rules for choosing between them in real analytical queries

---

Prerequisites

You should be comfortable writing basic SELECT statements with WHERE clauses. If you need a refresher, start with SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries before continuing here. You should also have a rough sense of what aggregate functions like COUNT() do — if that's new territory, skim Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for Beginners first.

---

What Does DISTINCT Actually Do?

DISTINCT is a modifier you put right after SELECT. Its job is simple: eliminate duplicate rows from your results. It doesn't group anything, it doesn't count anything, it doesn't summarize anything. It just filters your output so that every row you see is unique.

Here's a concrete example. Suppose you have an orders table that looks like this:

order_id	customer_id	product_id	order_date
1001	C042	P11	2024-11-03
1002	C017	P22	2024-11-05
1003	C042	P33	2024-11-07
1004	C099	P11	2024-11-10
1005	C017	P44	2024-11-12

Customer C042 placed two orders. Customer C017 placed two orders. If you want to see which customers made any purchase at all — without listing them twice — you use DISTINCT:

SELECT DISTINCT customer_id
FROM orders;

Result:

customer_id
C042
C017
C099

Three rows, not five. Every duplicate has been removed. That's the whole job of DISTINCT.

This is one of the most common sources of confusion. Let's make it concrete:

SELECT DISTINCT customer_id, product_id
FROM orders;

Result:

customer_id	product_id
C042	P11
C042	P33
C017	P22
C017	P44
C099	P11

Five rows again, because each customer-product combination really is unique in our data. DISTINCT didn't help us here because the rows were already distinct at that level of granularity.

---

What Does GROUP BY Actually Do?

GROUP BY does something fundamentally different. It doesn't just remove duplicates — it collapses rows with the same value into a single group, and then lets you run calculations per group. Think of it as sorting your data into buckets, one bucket per unique value, and then doing math on each bucket.

Without an aggregate function, GROUP BY and DISTINCT will often return the same list of values. But GROUP BY only becomes truly useful when you pair it with aggregates like COUNT(), SUM(), AVG(), or MAX().

Here's the same question as before — which customers placed orders? — answered with GROUP BY:

SELECT customer_id
FROM orders
GROUP BY customer_id;

Result:

customer_id
C042
C017
C099

Same output as DISTINCT, at least at first glance. But now let's ask a follow-up question: how many orders did each customer place?

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Result:

customer_id	order_count
C042	2
C017	2
C099	1

You cannot do this with DISTINCT alone. DISTINCT has no mechanism for counting or summarizing — it just filters. This is the key reason GROUP BY exists.

---

The Overlap: Simple Deduplication Queries

When you just want a list of unique values with no calculations attached, DISTINCT and GROUP BY are genuinely equivalent — at least in terms of the results they return. Most SQL engines will even execute them with the same query plan in simple cases.

-- Using DISTINCT
SELECT DISTINCT customer_id
FROM orders;

-- Using GROUP BY
SELECT customer_id
FROM orders
GROUP BY customer_id;

Both return the same list of unique customer IDs.

So which should you use? Convention favors DISTINCT for pure deduplication — it communicates intent more clearly. When a colleague reads SELECT DISTINCT, they immediately know you're asking "what are the unique values?" When they read GROUP BY without any aggregates, it's less obvious what the query is trying to do.

---

COUNT(DISTINCT ...): The Bridge Between Both Worlds

Here's where things get really powerful — and where a lot of analysts get tripped up.

Suppose your manager asks: "How many unique customers placed an order last month?" You need a single number, not a list. You can't just use DISTINCT — that gives you a list of rows, not a count. And you can't just use COUNT(*) — that counts all rows including duplicates.

The answer is COUNT(DISTINCT column_name):

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_date >= '2024-11-01'
  AND order_date < '2024-12-01';

Result:

unique_customers
3

This is DISTINCT living inside an aggregate function. It tells SQL: "First deduplicate by customer_id, then count the remaining values."

Compare that to the naive approach:

-- This counts rows, not unique customers
SELECT COUNT(*) AS total_order_rows
FROM orders;
-- Returns 5, not 3

The difference between 5 and 3 might seem small in a toy example, but in a real orders table with tens of thousands of rows and customers who buy repeatedly, this discrepancy can be enormous.

---

GROUP BY with Aggregates: The Real Analytical Power

Now let's look at a more realistic scenario that shows why GROUP BY is indispensable for reporting.

Your company has two tables: orders and customers. You want to answer: "For each region, how many unique customers placed an order, and what was the total revenue?"

SELECT
    c.region,
    COUNT(DISTINCT o.customer_id) AS unique_customers,
    SUM(o.order_total) AS total_revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.region
ORDER BY total_revenue DESC;

Let's unpack what's happening here:

1. The JOIN brings in region data from the customers table. (If JOINs are new to you, SQL JOINs Explained with Real-World Examples is worth a read.)
2. GROUP BY c.region creates one bucket per region.
3. Inside each bucket, COUNT(DISTINCT o.customer_id) counts unique customers — because some customers might have multiple orders within the same region.
4. SUM(o.order_total) adds up all revenue in that region.

This query requires GROUP BY. There's no way to produce per-region aggregates with DISTINCT alone.

Sample result:

region	unique_customers	total_revenue
West	412	189,440.00
Northeast	389	162,890.00
South	274	98,320.00
Midwest	198	77,150.00

---

Grouping on Multiple Columns

GROUP BY can bucket on more than one column at a time. When it does, each unique combination of those columns becomes a separate group.

SELECT
    c.region,
    YEAR(o.order_date) AS order_year,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.region, YEAR(o.order_date)
ORDER BY c.region, order_year;

This gives you unique customer counts broken down by both region and year — so you can see how each region's customer base grew or shrank year over year.

---

A Common Trap: Inflated Counts from JOINs

This is one of the sneakiest bugs in SQL, and DISTINCT is often the bandage people reach for without understanding the underlying problem.

Suppose you JOIN orders to an order_line_items table (which stores individual products within each order). If a single order has three line items, that order row will now appear three times after the JOIN.

-- Bug: this counts order rows, not unique orders
SELECT
    customer_id,
    COUNT(order_id) AS order_count
FROM orders o
JOIN order_line_items li ON o.order_id = li.order_id
GROUP BY customer_id;

Because of the JOIN, every order_id appears multiple times — once per line item. So your order counts will be inflated.

The quick fix most beginners reach for:

-- Works, but understand WHY you're doing it
SELECT
    customer_id,
    COUNT(DISTINCT order_id) AS order_count
FROM orders o
JOIN order_line_items li ON o.order_id = li.order_id
GROUP BY customer_id;

Using COUNT(DISTINCT order_id) now correctly counts each order once, regardless of how many line items it has.

---

Performance: Does the Choice Matter?

In small datasets, the difference is negligible. In production tables with millions of rows, it can absolutely matter.

Here's the general picture:

• DISTINCT on a single column is typically fast. The database deduplicates as it scans.
• GROUP BY with aggregates requires the database to bucket rows, which can involve a sort or hash operation. Modern query optimizers handle this well, but it's more work than pure deduplication.
• COUNT(DISTINCT column) is often the most expensive of the three. The database needs to track every value it's seen in order to avoid counting duplicates. On large datasets with high cardinality (many unique values), this can be slow.

If performance becomes a bottleneck with COUNT(DISTINCT ...), one alternative is to pre-aggregate in a subquery:

-- Instead of counting distinct customer_ids directly,
-- first deduplicate them, then count the resulting rows
SELECT COUNT(*) AS unique_customers
FROM (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= '2024-11-01'
) AS deduped_customers;

This can sometimes be faster because the inner query deduplicates first, and the outer query only needs to count the resulting rows. Whether it's actually faster depends on your database engine and your data — always test.

---

Hands-On Exercise

Let's put this into practice. Use the following scenario and write the queries yourself before checking the answers.

Setup: You have a table called support_tickets with these columns:

• ticket_id (unique per ticket)
• customer_id
• agent_id
• category (e.g., 'Billing', 'Technical', 'Account')
• status ('Open', 'Resolved', 'Escalated')
• created_date
• resolution_date (NULL if not resolved)

Exercise 1: Write a query that returns a list of unique categories in the table. Use DISTINCT.

SELECT DISTINCT category
FROM support_tickets;

Exercise 2: Write a query that shows how many tickets exist in each category.

SELECT
    category,
    COUNT(*) AS ticket_count
FROM support_tickets
GROUP BY category;

Exercise 3: Write a query that shows, for each agent, the number of unique customers they've worked with.

SELECT
    agent_id,
    COUNT(DISTINCT customer_id) AS unique_customers_served
FROM support_tickets
GROUP BY agent_id
ORDER BY unique_customers_served DESC;

Exercise 4 (challenge): Write a query that shows each agent's ticket count, unique customer count, and the number of unresolved tickets (where resolution_date is NULL). Order by total ticket count, descending.

SELECT
    agent_id,
    COUNT(*) AS total_tickets,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(CASE WHEN resolution_date IS NULL THEN 1 ELSE 0 END) AS unresolved_tickets
FROM support_tickets
GROUP BY agent_id
ORDER BY total_tickets DESC;

---

Common Mistakes & Troubleshooting

Mistake 1: Using DISTINCT when you need GROUP BY

-- Wrong: returns a list, not a count per category
SELECT DISTINCT category
FROM support_tickets;

-- Right: returns the count per category
SELECT category, COUNT(*) AS ticket_count
FROM support_tickets
GROUP BY category;

If you find yourself writing SELECT DISTINCT and then trying to also include aggregate functions, stop — you need GROUP BY instead.

Mistake 2: Forgetting that DISTINCT applies to all selected columns

-- This does NOT give you unique customer_ids
-- It gives you unique (customer_id, status) combinations
SELECT DISTINCT customer_id, status
FROM support_tickets;

If you want unique customers only, select only customer_id, or use GROUP BY customer_id.

Mistake 3: Using COUNT(*) when you need COUNT(DISTINCT ...)

-- Wrong: counts rows, including one customer submitting many tickets
SELECT COUNT(*) FROM support_tickets; -- might return 5,000

-- Right: counts unique customers who submitted tickets
SELECT COUNT(DISTINCT customer_id) FROM support_tickets; -- might return 1,200

Always ask yourself: am I trying to count rows or count unique values of something?

Mistake 4: Missing columns from GROUP BY

-- This will error in most databases
SELECT category, status, COUNT(*)
FROM support_tickets
GROUP BY category;
-- Error: 'status' is not in GROUP BY and is not aggregated

-- Fix: add status to GROUP BY
SELECT category, status, COUNT(*)
FROM support_tickets
GROUP BY category, status;

Mistake 5: Confusing filtering with HAVING vs WHERE

After you've grouped data, you can't filter on aggregate results using WHERE. You need HAVING. For example:

-- Wrong
SELECT agent_id, COUNT(*) AS ticket_count
FROM support_tickets
WHERE COUNT(*) > 50
GROUP BY agent_id;

-- Right
SELECT agent_id, COUNT(*) AS ticket_count
FROM support_tickets
GROUP BY agent_id
HAVING COUNT(*) > 50;

For a full treatment of HAVING, see Filtering Groups After Aggregation: Writing HAVING Clauses That Answer Real Business Questions.

---

Summary & Next Steps

Here's what you've learned, distilled:

Scenario	Tool
Get a list of unique values	SELECT DISTINCT column
Count unique values	COUNT(DISTINCT column)
Count, sum, or average per group	GROUP BY with aggregate functions
Multiple columns, no aggregates	Either works; prefer DISTINCT for clarity
Per-group summaries with multiple metrics	GROUP BY only

The mental question to ask yourself every time: Am I just listing unique values, or am I calculating something per group? If the answer is the former, reach for DISTINCT. If it's the latter, you need GROUP BY.

The deeper skill here is understanding that both tools serve different levels of analysis. DISTINCT is a deduplication filter you use when your output just needs to show unique values. GROUP BY is an aggregation engine you use when you need to summarize data at a particular level of granularity. COUNT(DISTINCT ...) is the bridge that lets you count unique values as part of a larger aggregation.

Where to go next:

• For a deeper exploration of what you can do once you have groups, including how to filter aggregated results, see Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG.
• Once you're comfortable with grouping, learn how to aggregate across multiple related tables in Multi-Table Reporting with JOIN and GROUP BY: Aggregating Across Relationships in a Single Query.
• If you want to understand how SQL can rank and compare values within groups — going beyond simple counts — Window Functions: RANK, ROW_NUMBER, and LAG is a natural next step.