How many?
NOVA’s marketing lead stops by Harry’s desk with a question that sounds almost too simple: “How many customers do we actually have?”
Easy. SELECT * FROM customers, then I count the rows in the result panel.
You already know how to make the database do work for you — WHERE, ORDER BY. Counting is no different. Let SQL count; don't count with your eyes.
COUNT(*) is an aggregate function: instead of handing back one row per row of the table, it collapses everything down into a single number. COUNT(*) specifically counts rows — it doesn’t look inside them at all, it just tallies how many made it through.
How many rows am I looking at?
SELECT COUNT(*) AS total_customers FROM customers;| total_customers |
|---|
| 12 |
Marketing: “Nice. Now — how many of them have actually given us a phone number?” Harry swaps the star for a column name:
SELECT COUNT(*) AS total_customers, COUNT(phone) AS with_phone
FROM customers;| total_customers | with_phone |
|---|---|
| 12 | 9 |
Same table, two different numbers. COUNT(*) counted every row. COUNT(phone) only counted rows where phone actually has a value — the three customers with nothing on file (NULL) just don’t get counted. That’s the real difference between COUNT(*) and COUNT(column): one counts rows, the other counts values that are actually there.
Harry: “Okay, one more — how many different countries are our customers from? COUNT(country), right?”
SELECT COUNT(country) AS country_count FROM customers;| country_count |
|---|
| 12 |
12? That's not right. We definitely don't sell to 12 different countries.
Right, because COUNT(country) doesn't know about "different" — it just counts every row that has a country filled in. Every customer has one, so it counted all 12, duplicates included. You don't want to count values. You want to count distinct values.
SELECT COUNT(DISTINCT country) AS distinct_countries FROM customers;| distinct_countries |
|---|
| 7 |
DISTINCT collapses duplicate values before COUNT ever sees them. India shows up twice in the table, USA three times, Brazil twice — but as far as COUNT(DISTINCT country) is concerned, each of those is one country, counted once.
Running…
One query, two honest answers: 12 customers, spread across 7 countries.
Try it: What do COUNT(*) and COUNT(phone) return for just the Indian customers?
Run your query to see results here.
Two customers, one phone number. Alice Sharma has one on file; Farah Khan doesn’t — her phone is NULL, so COUNT(phone) skips her row entirely. WHERE runs first and narrows the rows down to India; COUNT then does its counting on whatever’s left. Filtering and aggregating stack cleanly on top of each other.
Ops wants a quick sanity check on the orders table: the total number of orders NOVA has ever received, and — in that same query — the number of distinct customers who’ve placed at least one of them.
Hint 1
You need two numbers out of one query — can a single SELECT return more than one aggregate at a time? Separate them with a comma.
Hint 2
COUNT(*) counts every order row, no matter who placed it. For the customer number, you want COUNT of a column, but with duplicates collapsed first.
Hint 3
SELECT COUNT(*) AS total_orders, COUNT(DISTINCT customer_id) AS distinct_customers FROM orders;
Solution
Running…
Total orders and distinct customers are almost never the same number. NOVA’s regulars — customers 1 and 5 among them — place several orders each, so the order count runs well ahead of the customer count. That gap between “how many rows” and “how many distinct somethings” is exactly the kind of thing COUNT and DISTINCT are built to surface.
COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts unique values, duplicates collapsed. Three different questions, three different numbers.