Data · SQL
Difference between COUNT(*) and COUNT(column) in SQL
A table with 4 rows: what does COUNT(*) return?
If one of the 4 rows has a NULL phone, what does COUNT(phone) return?
From COUNT(*) to a count after a LEFT JOIN, in five steps:
Why does COUNT(column) return less than COUNT(*)?
COUNT(*) counts rows; COUNT(phone) skips NULL cells. On 12 rows with 3 NULL phones, the first returns 12 and the second returns 9.
Let’s take five customers, two of them with no phone.
SELECT COUNT(*),
COUNT(phone)
FROM customers| id | name | phone |
|---|---|---|
| 1 | Emma | (312) 555-0148 |
| 2 | Noah | (415) 555-0193 |
| 3 | Mia | NULL |
| 4 | Liam | (617) 555-0162 |
| 5 | Ava | NULL |
| COUNT(*) | COUNT(phone) |
|---|---|
| 5 | 3 |
Which rows does
COUNT(phone)skip? Tap them in the table.Go down the
phonecolumn and look for NULL.
SELECT COUNT(phone),
COUNT(*) - COUNT(phone)
FROM customers| id | name | phone |
|---|---|---|
| 1 | Emma | (312) 555-0148 |
| 2 | Noah | NULL |
| 3 | Mia | (206) 555-0127 |
| 4 | Liam | (617) 555-0162 |
| 5 | Ava | (512) 555-0185 |
| 6 | Ethan | NULL |
Does COUNT(column) count zero values?
Only NULL is skipped: a $0 discount is a value. Out of 10 orders, with 3 NULL discounts and 4 discounts of $0, COUNT(discount) returns 7.
Let’s take five orders and their discounts.
SELECT COUNT(discount) FROM orders
| id | discount |
|---|---|
| 101 | $5 |
| 102 | $0 |
| 103 | NULL |
| 104 | $10 |
| 105 | $0 |
| COUNT(discount) |
|---|
| 4 |
Order 102 has a $0 discount. Does
COUNT(discount)count this row?
SELECT COUNT(discount) FROM orders
| id | discount |
|---|---|
| 101 | $10 |
| 102 | $0 |
| 103 | NULL |
| 104 | $0 |
| 105 | $5 |
What does COUNT(DISTINCT column) count?
COUNT(DISTINCT city) counts each city only once and skips NULL. A column that holds Austin 6 times and Denver 3 times returns 2.
Let’s take five customers and their cities.
SELECT COUNT(DISTINCT city) FROM customers
| id | name | city |
|---|---|---|
| 1 | Emma | Austin |
| 2 | Noah | Denver |
| 3 | Mia | Austin |
| 4 | Liam | NULL |
| 5 | Ava | Boston |
| COUNT(DISTINCT city) |
|---|
| 3 |
Austin appears twice. How many times does
COUNT(DISTINCT city)count it?Liam’s city is NULL. Does
COUNT(DISTINCT city)count it as a city?
SELECT COUNT(city),
COUNT(DISTINCT city)
FROM customers| id | name | city |
|---|---|---|
| 1 | Emma | Denver |
| 2 | Noah | Boston |
| 3 | Mia | Denver |
| 4 | Liam | Denver |
| 5 | Ava | NULL |
| 6 | Ethan | Miami |
Why does COUNT(*) return 1 for a customer with no orders?
After a LEFT JOIN, a customer with no orders, say the one whose id is 7, keeps one row where orders.id is NULL. For that customer, COUNT(*) returns 1 and COUNT(orders.id) returns 0.
Let’s take three customers, one of them with no orders.
SELECT customers.name, COUNT(*) AS num_rows, COUNT(orders.id) AS num_orders FROM customers LEFT JOIN orders ON orders.customer_id = customers.id GROUP BY customers.name
| id | name |
|---|---|
| 1 | Emma |
| 2 | Noah |
| 3 | Mia |
| id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 1 |
| 103 | 3 |
| customers.name | orders.id |
|---|---|
| Emma | 101 |
| Emma | 102 |
| Noah | NULL |
| Mia | 103 |
| name | num_rows | num_orders |
|---|---|---|
| Emma | 2 | 2 |
| Noah | 1 | 0 |
| Mia | 1 | 1 |
Which other customer has the same
num_rowsas Noah?
SELECT customers.name, COUNT(*) AS num_rows, COUNT(orders.id) AS num_orders FROM customers LEFT JOIN orders ON orders.customer_id = customers.id GROUP BY customers.name
| id | name |
|---|---|
| 1 | Emma |
| 2 | Noah |
| 3 | Mia |
| 4 | Liam |
| id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 3 |
| 103 | 3 |
How do you check a SQL COUNT quickly?
It goes faster if you first read what is inside the parentheses: * counts rows, a column counts its cells that are not NULL, and DISTINCT keeps each value once.
SELECT COUNT(discount) FROM orders
orders id discount 101 $0 102 NULL 103 $20 104 $0 105 NULL 106 $5 SELECT COUNT(DISTINCT city) FROM customers
customers id name city 1 Emma Boston 2 Noah Austin 3 Mia NULL 4 Liam Boston 5 Ava Miami SELECT customers.name, COUNT(*) AS total FROM customers LEFT JOIN orders ON orders.customer_id = customers.id GROUP BY customers.name
customers id name 1 Emma 2 Noah 3 Mia 4 Liam orders id customer_id 101 1 102 1 103 2 104 4 SELECT customers.name, COUNT(orders.id) AS total FROM customers LEFT JOIN orders ON orders.customer_id = customers.id GROUP BY customers.name
customers id name 1 Emma 2 Noah 3 Mia 4 Liam 5 Ava orders id customer_id 101 2 102 3 103 3 104 3 105 4