Counting Values

Instead of the star, you can write a column in the parentheses of COUNT. COUNT(column) doesn't count rows but values: the rows in which the column isn't NULL. Two of the shop's customers have no city, so these two numbers differ:

SELECT COUNT(*) AS all_customers,
       COUNT(city) AS with_city
FROM customers;

COUNT(*) counts all 14 rows, COUNT(city) only the 12 that have a city. NULL means that there's no value, and where there's no value, there's nothing to count. A column that is never NULL, such as id, gives the same number as COUNT(*).

To count each value only once, write DISTINCT in the parentheses, before the column. How many different cities do the customers live in?

SELECT COUNT(DISTINCT city) AS cities
FROM customers;

In four. SELECT DISTINCT city FROM customers; would show five rows, because NULL is one of them, but COUNT(DISTINCT city) skips NULL, just as COUNT(city) does.

Typical mistake: COUNT(column) where you want to count rows. SELECT COUNT(released) FROM games; says 18, although the shop has 20 games: Robot Rebellion and Mystery Manor haven't come out yet, so their released is NULL. To count rows, write COUNT(*), and put a column in the parentheses only when you want to leave out the rows where it's NULL.