Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
ROW COUNT
2.1.
Syntax
2.2.
Parameters
3.
Row Count in a Single Table
3.1.
Example
4.
Row Count in Multiple Tables
4.1.
By Query
4.2.
By UNION Operator
5.
Row Count in a Database
6.
Frequently Asked Questions
6.1.
Write the statement to count the number of rows in a MySQL table.
6.2.
Does the row count in MySQL include deleted or inactive rows?
6.3.
Write the difference between “Select count()” and “Select count(column_name)” for row count in MySQL.
6.4.
How can we combine multiple tables to get a row count in MySQL?
6.5.
Are there any restrictions and limitations on the row count in MySQL?
7.
Conclusion
Last Updated: Mar 27, 2024
Easy

MySQL ROWCOUNT

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

Hey learners, do you know how we can find the number of rows in a table? In this article, we will determine the total number of rows present in a table. MySQL provides you “SELECT COUNT(*)” statement, which returns a single value representing the total row count.

MySQL ROWCOUNT

Let’s begin our journey to learn what row count is and how we can find the number of rows in a table.

ROW COUNT

The heading clears it that we are going to count the number of rows or records of the table or database. MySQL rowcount provides many ways to find the row count of a table by using the COUNT() function. In this topic, we’ll learn how to get the number of row counts from multiple tables within a database.

Syntax

Here is the basic syntax for counting the number of rows in a table.

SELECT COUNT(Expression) FROM tablename WHERE Condition;

Parameters

Below are the parameters that we are using.

  1. tablename: Name of the table for which you want to count the number of rows.
     
  2. Condition: Condition denotes for which particular condition you are finding the number of rows. If you remove the condition then it will count all the rows.
     
  3. Expression: It denotes for which you are finding the count. Like if you use *, it will give you the count of non-null values in that column.
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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:

Student Table

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

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

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  

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

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

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:

Databases

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

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

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

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.

And many more on our platform Coding Ninjas Studio.

Refer to our Guided Path to upskill yourself in DSACompetitive ProgrammingJavaScriptSystem 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 problemsinterview experiences, and interview bundles for placement preparations.

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

Happy Learning!

Live masterclass