Counting Rows with COUNT() and Its Variations

The COUNT() function is one of the most popular and useful aggregate functions in SQL. Its main job is to count the number of rows in your query results. If COUNT() were a superhero in the SQL world, its superpower would be quickly answering questions like:

• How many employees work at the company?
• How many students are enrolled in each department?
• How many products were sold last month?

The syntax for COUNT() is pretty simple:

COUNT(column)

where column is the name of the column whose rows you want to count. But there are other ways to use it, which we'll break down in this lecture.

Let's start with the most basic use of COUNT().

Option 1: Counting All Rows with COUNT(*)

When you want to count every row in a table, no matter if there are missing values or not, you use COUNT(*). The asterisk means "all columns".

Example: We have a students table with the following data:

Let's run this query:

SELECT COUNT(*) 
FROM students;

Result:

The COUNT(*) function doesn't care about NULL values in individual columns, since it just counts the number of rows in the table.

Option 2: Counting Rows with Non-NULL Values in a Column COUNT(column)

But what if you only want to count rows where a specific column is not NULL? In that case, use COUNT(column).

Example: Let's count how many students have a name specified.

SELECT COUNT(name)
FROM students;

Result:

Notice the difference? There are 4 rows in the table, but one of them has NULL in the name column. The COUNT(column) function ignores rows where the column value is NULL.

Comparing COUNT(*) and COUNT(column)

So, what's the actual difference between these two ways of using the function: COUNT(*) and COUNT(column)?

• COUNT(*) counts all rows in the table, including rows with NULL in any columns.
• COUNT(column) only counts rows where the specified column is not NULL.

Here's a sample table:

Queries:

-- Counts all rows.
SELECT COUNT(*) FROM students;         -- 4  -- TOTAL (all rows)

-- Counts only rows where name is not NULL.
SELECT COUNT(name) FROM students;      -- 3  -- Counting rows with a name

-- Counts only rows where age is not NULL.
SELECT COUNT(age) FROM students;        -- 3  -- Counting rows with an age

Option 3: Counting Unique Values with COUNT(DISTINCT column)

Sometimes you want to count only unique values in a column. For example, you want to know how many unique ages are listed for students. That's where COUNT(DISTINCT column) comes in.

Example:

SELECT COUNT(DISTINCT age) FROM students;

Result:

Notice that in this case, DISTINCT ignores not only duplicates but also NULL values.

If you try to use DISTINCT with COUNT(*), you'll get an error: DISTINCT can only be used with specific columns.

Examples of Using COUNT() in Real Tasks

Example 1. Counting the number of students

SELECT COUNT(*) AS total_students
FROM students;

Result:

Example 2. Counting students with a known age

SELECT COUNT(age) AS students_with_age
FROM students;

Result:

Example 3. Counting unique ages

SELECT COUNT(DISTINCT age) AS unique_ages
FROM students;

Result:

Common Mistakes When Using COUNT()

Expecting COUNT(column) to count all rows, even if there are NULLs.

That's not true: COUNT(column) ignores rows where the specified column is NULL.

Using COUNT(*) to count unique values.

Instead, use COUNT(DISTINCT column).

Forgetting about filtering when counting specific data.

For example:

SELECT COUNT(*) FROM students WHERE age > 20;

Here, you'll only get students older than 20, because WHERE filters the rows before counting.

These little things often lead to logical mistakes in queries. Stay sharp!