Count a Number of Records in a Database Table

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 |
+-------------------------+

• « Previous page
• Next page »