MySQL ROWCOUNT

Row Count in a Single Table

By the following steps, we can find the row count in a table.

Step 1: Select the database in which your table resides and use the below command to select the database.

USE database_name;

Step 2: Find the row count of the desired table by using the below syntax.

SELECT COUNT(*) FROM tablename WHERE condition;

Example

Let’s take an example for more clarification to find the count in a single table. Take a look at the below example.

Student Table

First, we have created a student table with entities like name, rollnumber, and branch. To display the Student table use the below query.

SELECT * from student;

Output:

Without Condition

Let’s find the row count in the above student table without any conditions. We use the below command to count the number of rows.

SELECT COUNT(*) FROM student;

Output

With Condition

Suppose you want to count the rows that have the branch "CS". So you will use the below command to count the number of rows in the student table.

SELECT COUNT(*) FROM student WHERE branch= "CS";

Output

Explanation

The compiler will check if there is any CS present in the column. If yes, then it will increase the value of the count by 1. Similarly, it will check each row one by one and print the final count as shown in the output.

Row Count in Multiple Tables

MySQL provides you to get the number of rows of two or more tables. We are going to find the row count using two ways.

• By using queries and,
• By using the Union operator.

By Query

We need two tables to perform this task. So, we will use the student table that we created above. We will create one more table, which is the Teacher’s table and that is shown below:

Teacher Table

To display the created Teacher table, use the below query.

SELECT * from teacher;

Output  

In order to get the row count of the Student and Teacher table, by following the below command, you will get the number of rows.

Example

SELECT
(SELECT COUNT(*) FROM student) As firstTable,
(SELECT COUNT(*) FROM teacher) As secondTable;

Output

Explanation

You can clearly see that in the student table, there are 5 rows, and in the teacher table, only 6 rows are there.

By UNION Operator

With the help of UNION operator, you can combine the results of two or more SELECT statements. To know more about UNION operator then click here.

In this method, by using the Union operator, we will find the number of rows in the Student and Teacher table in the below example.

Example

SELECT 'student' firstTable, COUNT(*) AS row1 FROM student UNION SELECT 'teacher' secondTable, COUNT(*) AS row2 FROM teacher;

Output

Explanation

The Union operator is used to combine the result of two or more tables, so by using the Union operator in the above query, we are finding the rows in the Student and Teacher table.

Row Count in a Database

In order to find the total rows of all tables in a particular database, we can use TABLE_ROWS with the function SUM.

Step 1: Show databases

To perform the ROW COUNT in the database, first, you have to show all the databases in your MySQL. You can use the below command to do so.

show databases;

Output:

Step 2: Select the database

Now you have to select the database in which you want to find the total number of rows. Suppose you want to find it in the “mysql” database, then use the below command to do so.

use mysql;

Output

Step 3: Display the count of all rows per table

SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='mysql'
ORDER BY table_name;

Output

Explanation

In the above query, we are using INFORMATION_SCHEMA, which is a way of querying data from the INFORMATION_SCHEMA database directly. So this will help to count the number of rows from the tables in the database.

Step 4: Find the total sum of Table_Rows

To find the sum of the “Table_Rows” attributes in the above table, apply the following query in order to get the row count of all the tables in a particular database.

SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mysql';

Output

Explanation

By using the above query, we are finding the total number of row counts of all the tables in the ‘mysql’ database.

Frequently Asked Questions

Write the statement to count the number of rows in a MySQL table.

To find the row count, you can use the “SELECT COUNT()” statement in the SQL query.

Does the row count in MySQL include deleted or inactive rows?

The Row count in MySQL includes all the rows present in a table. This also contains deleted or inactive rows.

Write the difference between “Select count()” and “Select count(column_name)” for row count in MySQL.

“Select count()” counts all the rows in a table, and on the other hand, “Select count(count_name)” counts only the rows where the specified column has a non-null value.

How can we combine multiple tables to get a row count in MySQL?

You can use the JOIN operation to combine multiple tables and then apply the row count query.

Are there any restrictions and limitations on the row count in MySQL?

There are no restrictions and limitations on the row count in MySQL. However, extremely large row counts may contain significant memory and processing resources.

Conclusion

In this article, we discussed the COUNT() statement and its main operations on tables and databases. We hope this blog has helped you enhance your knowledge of row count in MySQL. Check the below articles to know more.

• Create Table in SQL
• Difference between CHAR and VARCHAR
• MySQL ISNULL
• Update Query in MySQL
   

And many more on our platform Coding Ninjas Studio.

Refer to our Guided Path to upskill yourself in DSA, Competitive Programming, JavaScript, System Design, and many more! If you want to test your coding ability, you may check out the mock test series and participate in the contests hosted on Coding Ninjas Studio!

But suppose you have just started your learning process and are looking for questions from tech giants like Amazon, Microsoft, Uber, etc. For that purpose, take a look at the problems, interview experiences, and interview bundles for placement preparations.

However, you may consider our paid courses to give your career an edge over others!

Happy Learning!