🎓 A1Learner
🔍

SQL Count

COUNT counts rows and returns one number. It is an aggregate function, a function that combines many rows into one result.

Count every row

The star inside COUNT means every row. The customers table holds eight rows.

SELECT COUNT(*) AS total_customers
FROM customers;

Count the values present

COUNT(city) counts rows where city holds a value. One customer has a NULL city, so the count is seven, not eight.

SELECT COUNT(city) AS cities_present
FROM customers;

Count rows that match

WHERE runs before the count here. Only the two customers in the USA are counted.

SELECT COUNT(*) AS usa_customers
FROM customers
WHERE country = 'USA';

Example

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

Key points

  • COUNT: an aggregate function that counts rows or values
  • COUNT(*) counts every row in the result, NULL values included.
  • COUNT(column) counts the rows where that column holds a value.
  • A NULL value is skipped by COUNT(column).
  • WHERE filters the rows before COUNT counts them.
  • An AS alias gives the count a clear column name.

In this example, one SELECT asks for both counts side by side. COUNT(*) counts all eight customer rows. COUNT(city) counts only the rows where city holds a value. The gap of one is the customer whose city is NULL.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

Why does COUNT(city) return 7 when COUNT(*) returns 8 on the customers table?