How to Count Number of Records in a Database Table in MySql

In order to count a number of records in a database table we can use standard SQL COUNT function. The syntax is quite simple as follows:

SELECT COUNT(*) AS total_founds
FROM table_name

The query will return the total record of the table. We can use WHERE clause in SELECT statement to filter records in the table we want to count as follows:

SELECT COUNT(*) AS total_founds
FROM table_name
WHERE conditions

The query will only returns the number of records or rows which match the conditions in WHERE clause.

Be noted that the COUNT(*) will return all rows in the database table even though a specified column value in a database table is NULL. In order to count records without NULL value in a specified column, we can use COUNT(column) instead. Here is the syntax:

SELECT COUNT(column)
FROM table_name

Let’s practice with a couple of examples to understand more how SQL COUNT works in MySQL:

We can count all the offices in the office table by executing following query:

 SELECT COUNT(*) AS total_office  FROM offices 

+--------------+
| total_office |
+--------------+
|            7 |
+--------------+

To count all office which has state we can operate following query:

SELECT COUNT(state) AS total_office_with_state
FROM offices 

+-------------------------+
| total_office_with_state |
+-------------------------+
|                       4 |
+-------------------------+

This article has been referred from

http://www.mysqltutorial.org/mysql-sql-count-record.aspx

count column, Count Records in a Database Table in MySQL, database table, founds, select statement, syntax

MySQL