Counting Rows with COUNT

Raw data often requires summarizing to provide valuable information. One of the primary summaries is simply counting things: How many customers do we have? How many orders were placed yesterday? How many unique products were sold? SQL provides a dedicated aggregate function for this: COUNT.

The COUNT function tallies the number of rows that satisfy certain criteria. Let's look at its common forms.

Counting All Rows: COUNT(*)

The most straightforward use of COUNT is with an asterisk (*). COUNT(*) simply counts the total number of rows in a table or a result set defined by a WHERE clause.

Syntax:

SELECT COUNT(*)
FROM table_name;

Example:

Imagine we have a Customers table like this:

customer_id	first_name	last_name	email	city
1	Alice	Smith	[email protected]	London
2	Bob	Jones	[email protected]	Manchester
3	Charlie	Brown	NULL	London
4	Diana	Prince	[email protected]	Birmingham
5	Edward	King	[email protected]	London

To find out the total number of customers in the table, you would run:

SELECT COUNT(*)
FROM Customers;

Result:

COUNT(*)
5

This query returns 5, because there are five rows in the Customers table.

Counting Non-NULL Values in a Column: COUNT(column_name)

Sometimes, you don't want to count all rows, but rather the number of rows that have a non-NULL value in a specific column. For this, you provide the column name inside the parentheses.

Syntax:

SELECT COUNT(column_name)
FROM table_name;

Example:

Using the same Customers table, let's count how many customers have provided an email address. We target the email column:

SELECT COUNT(email)
FROM Customers;

Result:

COUNT(email)
4

Notice the result is 4, not 5. This is because COUNT(email) ignores the row where the email column has a NULL value (customer_id 3). COUNT(*) counts rows, while COUNT(column_name) counts non-NULL values within that column across the rows.

Counting Distinct Values in a Column: COUNT(DISTINCT column_name)

Often, you need to know how many unique values exist in a column. For instance, how many different cities do our customers live in? The DISTINCT keyword used inside COUNT achieves this.

Syntax:

SELECT COUNT(DISTINCT column_name)
FROM table_name;

Example:

Let's count the number of distinct cities represented in our Customers table:

SELECT COUNT(DISTINCT city)
FROM Customers;

Result:

COUNT(DISTINCT city)
3

The query returns 3 because although there are 5 rows, the cities listed are 'London', 'Manchester', 'London', 'Birmingham', and 'London'. The distinct cities are London, Manchester, and Birmingham. NULL values, if present in the column, are not included in the DISTINCT count.

Using Aliases with COUNT

The default column name for a COUNT result (like COUNT(*) or COUNT(email)) isn't always descriptive. You can assign a more meaningful name using the AS keyword, which creates an alias.

Example:

Let's rename the output of our previous examples:

-- Total number of customers
SELECT COUNT(*) AS total_customers
FROM Customers;

-- Number of customers with email
SELECT COUNT(email) AS customers_with_email
FROM Customers;

-- Number of unique customer cities
SELECT COUNT(DISTINCT city) AS distinct_customer_cities
FROM Customers;

Results:

total_customers
5

customers_with_email
4

distinct_customer_cities
3

Using aliases makes the output much clearer and easier to understand, especially when you start combining multiple aggregate functions in one query.

Comparison of different counts from the Customers table: Total rows vs. Non-NULL emails vs. Distinct cities.

Combining COUNT with WHERE

Aggregate functions like COUNT are applied after the WHERE clause filters the rows. This means you can count rows that meet specific conditions.

Example:

How many customers live in London?

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

Result:

london_customers
3

The database first filters the Customers table to include only rows where city is 'London', and then COUNT(*) counts those filtered rows.

The COUNT function is a fundamental building block for summarizing data. You'll use it frequently, often in combination with the GROUP BY clause (which we'll cover shortly) to count items within different categories, such as counting the number of orders placed by each customer.