Unit 04.01: COUNT(*) versus COUNT(column)

Unit ID: SQL-M04-U02 - Estimated active time: 12-15 minutes Objective: choose the counting form that answers the question asked.

They count different things

• COUNT(*) counts rows.
• COUNT(column) counts non-NULL values in that column.
• COUNT(DISTINCT column) counts distinct non-NULL values.

SELECT COUNT(*)        AS customers,
       COUNT(country)  AS with_country
FROM customers;
-- 4812 | 4512

The gap is 300 - the customers with no recorded country from Module 3. Using COUNT(country) when you meant "how many customers" understates by exactly the number of missing values, silently.

The distinct-count question people actually ask

"How many customers ordered?" is not COUNT(*) on orders:

SELECT COUNT(*)                    AS orders,
       COUNT(DISTINCT customer_id) AS customers_who_ordered
FROM orders;
-- 1000 | 1000

Here they happen to be equal, because our seed gives each order a different customer. That coincidence is dangerous - the query is right for the wrong reason, and the day a customer places two orders it will quietly diverge. Write COUNT(DISTINCT customer_id) because it expresses the question, not because the numbers currently match.

Counting within groups

SELECT status,
       COUNT(*)                       AS orders,
       COUNT(DISTINCT customer_id)    AS customers
FROM orders
GROUP BY status;
-- completed | 988 | 988
-- pending   |  12 |  12

Carrying both makes any future divergence visible in the result itself rather than in a bug report.

Practice

Which counting form answers each question?

1. How many feedback responses did we receive?
2. How many people actually gave a rating?
3. How many different ratings values appear?

SELECT COUNT(*)                 AS responses,   -- 500
       COUNT(rating)            AS rated,       -- 440
       COUNT(DISTINCT rating)   AS distinct_vals -- 5
FROM feedback;

Takeaway

COUNT(*) for rows, COUNT(col) for values present, COUNT(DISTINCT col) for variety. Picking the wrong one produces a number that is quietly answering a different question.

---

Mark complete