COUNT vs COUNT DISTINCT in SQL
COUNT(*) counts rows, COUNT(column) skips empty values, and COUNT(DISTINCT column) counts different values. Count orders against unique customers, then use the same trick to spot a duplicate email.
Step by step
Rows vs people
- How many customers?. How many customers placed an order? People often count the rows in the orders table and report that number. But that gives you orders, not customers. COUNT(DISTINCT) gives the right answer.
COUNT star
- COUNT star. Start with SELECT COUNT(*) AS orders FROM orders;. COUNT(*) counts every row in the table. It returns 141. That is the number of orders, not customers, because some customers ordered many times.
SELECT COUNT(*) AS orders FROM orders;
COUNT DISTINCT
- COUNT DISTINCT. Now add a second column to the same query: COUNT(DISTINCT customer_id) AS customers. DISTINCT means each different customer_id is counted once, however many times it appears. Run it, and both numbers sit side by side in one row.
COUNT(*) AS orders, COUNT(DISTINCT customer_id) AS customers - Unique customers. The customers column shows 34. That is how many different customer IDs placed the 141 orders, and it is the number the question asked for.
Counting a column
- Skips nulls. Next, write a new query on the customers table: COUNT(*) AS customers and COUNT(email) AS emails. Counting a column skips NULL values. It returns 40 customers but only 38 emails. 2 customers have no email (NULL), so COUNT(email) left them out.
SELECT COUNT(*) AS customers, COUNT(email) AS emails FROM customers;
Spot duplicates
- Find duplicates. Add COUNT(DISTINCT email) AS unique_emails to the same query. It counts each different email once. Run it to see all three counts in one row.
COUNT(email) AS emails, COUNT(DISTINCT email) AS unique_emails - One email, twice. unique_emails shows 37, compared with 38 emails. So one email is on 2 customer records. That is probably a duplicate customer, worth raising as a data quality ticket.
Recap
- Three ways to count. COUNT(*) counts rows. COUNT(column) skips NULLs. COUNT(DISTINCT column) counts different values. Put them side by side, and the gaps between them show you missing or repeated values.
Common mistakes
- Reporting COUNT(*) as the number of customers. COUNT(*) on orders counts orders, so a customer who ordered 5 times is counted 5 times. When the question is about people, use COUNT(DISTINCT customer_id).
- Expecting COUNT(column) to count every row. COUNT(email) skips NULLs, which is why 40 customers gave only 38 emails. Use COUNT(*) for the row count. COUNT(*) minus COUNT(email) tells you how many emails are missing.
- Thinking COUNT(DISTINCT) counts NULL as a value. COUNT(DISTINCT email) skips NULLs too, so 37 means 37 different real emails. To find blanks, compare COUNT(*) with COUNT(email).
- Near-duplicates that DISTINCT treats as different. Depending on the database and its settings, ann@example.com and Ann@example.com may be counted as 2 values. Clean them first, for example COUNT(DISTINCT LOWER(TRIM(email))).
When a business analyst uses this
Before a CRM migration workshop, a business analyst runs COUNT(*), COUNT(email) and COUNT(DISTINCT email) on the customer table. The missing emails and repeated emails go into the RAID log as data quality issues to fix before sign-off.
Questions people ask
What is the difference between COUNT and COUNT DISTINCT in SQL?
COUNT(*) counts rows, and COUNT(column) counts the values that are not NULL, repeats included. COUNT(DISTINCT column) counts each different value only once, so 141 orders can come from 34 customers.
Is it COUNT DISTINCT or DISTINCT COUNT in SQL?
In SQL you write COUNT(DISTINCT column), with DISTINCT inside the brackets. SELECT DISTINCT COUNT(*) does something else: DISTINCT applies to the result rows, so you still get the plain row count.
Is distinct count the same as unique count?
Usually people mean the same thing: each different value counted once, which is what COUNT(DISTINCT) does. But "unique" sometimes means values that appear only once, and COUNT(DISTINCT) does not give you that.
