Exact count of all rows in MySQL database?

To exactly count all rows, you need to use the aggregate function COUNT(*). The syntax is as follows −

select count(*) as anyAliasName from yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table CountAllRowsDemo
   -> (
   -> Id int,
   -> Name varchar(10),
   -> Age int
   -> );
Query OK, 0 rows affected (1.49 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into CountAllRowsDemo values(1,'John',23);
Query OK, 1 row affected (0.15 sec)
mysql> insert into CountAllRowsDemo values(101,'Carol',21);
Query OK, 1 row affected (0.17 sec)
mysql> insert into CountAllRowsDemo values(201,'Sam',24);
Query OK, 1 row affected (0.13 sec)
mysql> insert into CountAllRowsDemo values(106,'Mike',26);
Query OK, 1 row affected (0.22 sec)
mysql> insert into CountAllRowsDemo values(290,'Bob',25);
Query OK, 1 row affected (0.16 sec)
mysql> insert into CountAllRowsDemo values(500,'David',27);
Query OK, 1 row affected (0.16 sec)
mysql> insert into CountAllRowsDemo values(500,'David',27);
Query OK, 1 row affected (0.19 sec)
mysql> insert into CountAllRowsDemo values(NULL,NULL,NULL);
Query OK, 1 row affected (0.23 sec)
mysql> insert into CountAllRowsDemo values(NULL,NULL,NULL);
Query OK, 1 row affected (0.13 sec)

Display all records from the table using a select statement. The query is as follows −

mysql> select *from CountAllRowsDemo;

The following is the output −

+------+-------+------+
| Id   | Name  | Age  |
+------+-------+------+
|    1 | John  | 23   |
|  101 | Carol | 21   |
|  201 | Sam   | 24   |
|  106 | Mike  | 26   |
|  290 | Bob   | 25   |
|  500 | David | 27   |
|  500 | David | 27   |
| NULL | NULL  | NULL |
| NULL | NULL  | NULL |
+------+-------+------+
9 rows in set (0.00 sec)

Here is how you can count an exact number of rows in the table using aggregate function count(*).

The query is as follows −

mysql> select count(*) as TotalNumberOfRows from CountAllRowsDemo;

The following is the output with the count of rows −

+-------------------+
| TotalNumberOfRows |
+-------------------+
|                 9 |
+-------------------+
1 row in set (0.00 sec)

Samual Sam

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• How to count all characters in all rows of a field in MySQL?
• Get record count for all tables in MySQL database?
• MySQL - How to count all rows per table in one query?
• How to know the exact number of table and columns in a MySQL database?
• Count number of rows in each table in MySQL?
• Select two random rows in a MySQL database?
• Count(*) rows from multiple tables in MySQL?
• Count all sorted rows in a matrix in C++
• Can we get total number of rows in a MySQL database?
• Fastest way to count number of rows in MySQL table?
• How to find rows with exact value in one or more columns with MySQL?
• Get the sum of a column in all MySQL rows?
• MySQL query to count rows in multiple tables
• How to count the number of tables in a MySQL database?
• Get the count of only unique rows in a MySQL column?