How to Count the Number of Rows in a Table in SQL

Problem:

You’d like to determine how many rows a table has.

Example:

Our database has a table named pet with data in the following columns: id, eID (electronic identifier), and name.

Let’s count all rows in the table.

Solution:

COUNT(*) counts the total number of rows in the table:

SELECT COUNT(*) as count_pet
FROM pet;

Here’s the result:

Instead of passing in the asterisk as the argument, you can use the name of a specific column:

SELECT COUNT(id) as count_pet
FROM pet;

In this case, COUNT(id) counts the number of rows in which id is not NULL.

Discussion:

Use the COUNT aggregate function to count the number of rows in a table. This function takes the name of the column as its argument (e.g., id) and returns the number of rows for this particular column in the table (e.g., 5).

As mentioned above, when you specify a column instead of the asterisk, the function will only count non-NULL values. Since id is the primary key of our table—and therefore has unique and non-NULL values—it’s a good candidate for counting the total number of rows in the table.

Of course, to count all rows, you can instead pass in the asterisk character as the argument to COUNT. This will count all rows, including those with a value of NULL in any column.

Here’s an example of counting the number of rows for a column that has NULL values:

SELECT COUNT(eID) as count_pet
FROM pet;

It’s recommended that you pass in a primary key column or the * character to the COUNT function to count the number of rows in a table. As we’ve seen, both approaches will yield the same result.