Back to list Standard SQL How to Count the Number of Rows in a Table in SQL Database: SQL PostgreSQL MS SQL Server Oracle MySQL SQLite Operators:COUNT 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. ideIDname 123456sparky 223457mily 3NULLlessy 4NULLcarl 534545maggy 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: count_pet 5 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; count_pet 3 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. Recommended courses: SQL Basics SQL Basics in SQL Server SQL Practice Set Recommended articles: A Beginner’s Guide to SQL Aggregate Functions An Introduction to Using SQL Aggregate Functions with JOINs See also: How to Count Distinct Values in SQL How to Order by Count in SQL? How to Sum Values of a Column in SQL? Tags: SQL PostgreSQL MS SQL Server Oracle MySQL SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.