How many customers does the shop have? Until now, you counted the rows in the result yourself. The database can count them:
sql
SELECT COUNT(*) FROM customers;text
count
-------
12COUNT is an . It takes many rows and returns one value.
12 rowscustomers
COUNT(*)
1 row: 12the result
Three ways to count
customers
| id | name | country | |
|---|---|---|---|
| 9 | Yuki Tanaka | yuki.t@example.com | JP |
| 10 | Hannah Weber | NULL | DE |
| 11 | Diego Lopez | diego.l@example.com | MX |
| 12 | Grace Okafor | grace.o@example.com | NG |
| You write | It counts | All 12 customers |
|---|---|---|
COUNT(*) | rows | 12 |
COUNT(email) | values that are not NULL | 11 |
COUNT(DISTINCT country) | different values that are not NULL | 9 |
COUNT(col) skips NULL. Choose COUNT(*) for "how many rows" and COUNT(col) for "how many rows have a value".
Count some rows
WHERE runs first. COUNT then counts the rows that WHERE kept:
sql
SELECT COUNT(*) FROM customers WHERE country = 'CA';The result is one row with the value 3. Give it a name with AS, as with any expression: COUNT(*) AS canadians.