Which function is used to return the number of records?

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.

Subscribe to our newsletter Join our monthly newsletter to be
notified about the latest posts.

Email address

How Do You Write a SELECT Statement in SQL?

What Is a Foreign Key in SQL?

Enumerate and Explain All the Basic Elements of an SQL Query