Counting Rows

So far, every query has shown rows: one row of the result for each row of the table that it found. But often, you want a single number instead. How many customers does the shop have? How many games has it sold? How much has it earned? For such questions, SQL has aggregate functions. An aggregate function takes many rows and turns them into one value.

The simplest one is COUNT(*). It counts rows:

SELECT COUNT(*)
FROM customers;

The result has one row and one column, and the column is named after what you typed, COUNT(*). With AS, you can give it a better name, as you did with calculations in lesson 2:

SELECT COUNT(*) AS customer_count
FROM customers;

COUNT(*) works together with WHERE: the database first finds the rows that meet the condition, and then counts only those. This is how many customers live in London:

SELECT COUNT(*) AS london_customers
FROM customers
WHERE city = 'London';

A query with an aggregate function gives one row even when no row meets the condition. The shop has no customers from Paris, so COUNT(*) says 0:

SELECT COUNT(*) AS paris_customers
FROM customers
WHERE city = 'Paris';

The star in COUNT(*) means whole rows: it counts them whatever is in them. In the next exercise, you'll see what happens when you put a column in the parentheses instead. And besides counting, aggregate functions can add up, average, and find the smallest and the largest value; you'll meet them all in this lesson.

Typical mistake: a column next to COUNT(*). The result of SELECT name, COUNT(*) FROM customers; has one row, so which of the 14 names should it show? Most databases report an error. SQLite doesn't; it shows the name of one of the customers:

It looks as if Emma Clark had 14 of something, which is nonsense. For now, ask either for rows or for a count, not for both in one query. Later in this lesson, you'll learn how to count, for example, the customers of each city.