How to Count Table Rows in MySQL

1. Counting All Rows in a MySQL Table
2. Adding Conditions to Count Rows in MySQL

This tutorial article will show you how to retrieve the count of records in a MySQL table. We will also show you how to apply conditions to count rows that match specific criteria.

Counting All Rows in a MySQL Table

To retrieve the count of records in a table, we can should the following simple query.

SELECT COUNT(*) FROM 'table name';

It will count all rows in the table as long as there is a value in at least one column.

Add the table’s name, supermarkets, to count the number of records. In this case, we wish to count the number of supermarkets across California.

SELECT COUNT(*) FROM supermarkets;

Outcome:

| ROW COUNT |
| :-------- |
| 4699      |

Adding Conditions to Count Rows in MySQL

You can add more conditions to specify what rows you want to count. This is helpful when looking for specific data with large tables.

Count Expression

Use Count(expression) to count the total number of rows without blank spaces in a specified column.

SELECT COUNT(city) FROM supermarkets;

Outcome:

| Row Count |
| --------- |
| 4697      |

There was a difference of 2 rows in the total count when removing rows with blank spaces in the city column. This result would provide a more accurate number of supermarkets in California.

Using Where to Specify Values

You can also count the number of supermarkets in one city by specifying as follows.

SELECT COUNT(*) FROM supermarkets WHERE city = 'San Diego'

Outcome:

| Row Count |
| --------- |
| 896       |

It specifies the number of supermarkets within the city of San Diego.

Grouping by Value

To count the number of supermarkets for each city, choose the column you want to use to group, in this case, the city, and place it after SELECT. Use the GROUP BY statement after the table name to determine which column is to be used to group data.

SELECT city, COUNT(*) FROM supermarkets GROUP BY city;

Outcome:

| city          | Count |
| ------------- | ----- |
| Los Angeles   | 2777  |
| San Francisco | 1024  |
| San Diego     | 896   |

The total value for all three cities is 4,697, which matches the value in the initial count(expression) statement.