COUNT variations (*,col,DISTINCT)

Concepts covered: Counting, Distinct Counting

COUNT comes in 3 forms and they answer 3 different questions. COUNT(*) counts rows in the group, whatever is in them. COUNT(column) counts rows where that column holds a value, skipping every NULL. COUNT(DISTINCT column) counts how many different values the column holds, with NULL ignored and each repeated value counted once. On a table with no nulls and no repeats all 3 return the same number, which is how the differences stay hidden until the day they matter.

5 customers, 3 email addresses

The customers table below has 5 rows. Alice, Carol and Eve have an email on file; Bob and Dave have NULL in that column, because the signup form made email optional. A query that counts customers and a query that counts emails look almost identical and return different numbers.

SELECT
  COUNT(*) AS total_customers,
  COUNT(email) AS customers_with_email
FROM customers

COUNT(*) returns 5: it never looks inside a row, so a row with a missing email counts like any other. COUNT(email) returns 3, because the function examines the email value on each row and increments only when the value is present. COUNT(1) and COUNT(customer_id) would also return 5 here, the first because the constant 1 is never null and the second because a primary key is never null. The difference between 5 and 3 is 2 customers the marketing team cannot reach, and the gap between the 2 counts is a ready-made data quality metric.

DISTINCT counts each value once

Add a country column to the same 5 customers: 3 live in the US and 2 in the UK. COUNT(country) returns 5, since every row has a country. COUNT(DISTINCT country) returns 2, because the function collects the set of values it has seen and counts the set. The 3 tabs below run the 3 forms over the identical table; switch between them and read the single number each one produces.

COUNT(*)

Every row counts, including the 2 customers with no email.

SELECT COUNT(*) AS total_customers
FROM customers

COUNT(col)

Only rows with a non-null email count.

SELECT COUNT(email) AS customers_with_email
FROM customers

DISTINCT

Each distinct country counts once, however many customers live there.

SELECT COUNT(DISTINCT country) AS countries
FROM customers

COUNT(DISTINCT email) on this table returns 3, the same as COUNT(email), because the 3 present emails are all different. If 2 customers shared an address the distinct count would drop to 2 while the plain count stayed at 3, and the distinct form is the one that answers how many inboxes a campaign actually reaches. Distinct counting is also the most expensive of the 3 forms, since the engine has to hold every value it has seen to know whether the next one is new; on a column with millions of values that memory cost is real, and the intermediate tier covers the approximate version that trades a little accuracy for a lot of memory.

Counts per group

Each form combines with GROUP BY, and putting 2 of them side by side in one query is where the counts start explaining each other. The orders table below holds 8 orders placed by 4 customers across 3 regions. Counting orders per region measures volume; counting distinct customers per region measures reach, and the 2 numbers tell different stories about the same region.

SELECT region,
  COUNT(*) AS orders,
  COUNT(DISTINCT customer_id) AS customers
FROM orders
GROUP BY region

North shows 4 orders from 2 customers, and 3 of those 4 came from customer 101. South shows 3 orders from 2 customers, East 1 from 1. A manager reading only the first column sees North as the stronger region; the second column shows that North depends on 1 repeat buyer. Change COUNT(DISTINCT customer_id) to COUNT(customer_id) and the second column collapses back into a copy of the first, because every order has a customer and nothing is deduplicated.

About this lesson

This section is part of the Aggregating: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.