The COUNT Function

What is COUNT?

COUNT is an aggregate function that counts rows and returns a single number. It's one of the most commonly used functions in SQL — any time you need to know "how many?", COUNT is the tool.

SELECT COUNT(*)
FROM coffee_shop_sales;

This returns one row containing a single number: the total count of rows in the table. The * inside COUNT(*) means "count every row regardless of column values".

COUNT(*) vs COUNT(column)

COUNT(*) counts every row, including rows that have NULL values in some columns. This is the most common form and is what you'll use most of the time.

COUNT(column_name) counts only the rows where that specific column is not NULL. Use this when you want to know how many rows have a value in a particular column.

-- Count every row in the table
SELECT COUNT(*) FROM coffee_shop_sales;

-- Count only rows where drink_type is not NULL
SELECT COUNT(drink_type) FROM coffee_shop_sales;

Combining COUNT with WHERE

WHERE filters the rows first, then COUNT counts only the rows that passed the filter. This lets you answer questions like "how many orders came from a specific restaurant?" or "how many members have a Premium membership?"

-- How many visits were made by Premium members?
SELECT COUNT(*)
FROM gym_member_visits
WHERE membership_type = 'Premium';

-- How many orders cost more than $25?
SELECT COUNT(*)
FROM online_bookstore_orders
WHERE price_usd > 25;

What COUNT returns

Unlike SELECT * which returns many rows, COUNT always collapses the result down to a single row containing a single number. You can give that number a name using AS.

-- Give the result a readable column name
SELECT COUNT(*) AS total_sales
FROM coffee_shop_sales;

The AS total_sales part is optional but makes your results much easier to read, especially when sharing queries with others.

Common mistakes

Writing COUNT() with nothing inside — COUNT() is invalid. You need either COUNT(*) or a column name like COUNT(drink_type).

Expecting multiple rows back — COUNT on its own always returns exactly one row. To get a count per group (e.g. count per category), you need GROUP BY, which is covered in a later topic.

Using COUNT to preview data — COUNT tells you how many rows exist but doesn't show you the rows themselves. Use SELECT * ... LIMIT 5 to preview data.