Counting rows in a table in MySQL
This article will show you how to retrieve the number of records in a MySQL table. We will also show you how to apply conditions to count the rows that meet a certain criteria.
Count all rows in a MySQL table
To retrieve the count of records in a table, we can do the following simple query.
SELECT COUNT(*) FROM 'table name';
It will calculate all rows in the table as long as there is a value in at least one column.
Add the name of the table supermarketsto count the number of records. In this case, we want to count the number of supermarkets in California.
SELECT COUNT(*) FROM supermarkets;
result:
| ROW COUNT |
| :-------- |
| 4699 |
Adding a condition to count rows in MySQL
You can add more conditions to specify the number of rows to count. This is helpful when looking for specific data with large tables.
Count Expressions
Use Count(expression)to count the total number of rows that have no spaces in the specified column.
SELECT COUNT(city) FROM supermarkets;
result:
| Row Count |
| --------- |
| 4697 |
When you remove the rows with spaces in the city column, the total counts differ by 2 rows. This result will provide a more accurate count of supermarkets in California.
Using Where to specify values
You can also calculate the number of supermarkets in a city by specifying as follows.
SELECT COUNT(*) FROM supermarkets WHERE city = 'San Diego'
result:
| Row Count |
| --------- |
| 896 |
It specifies the number of supermarkets within the city of San Diego.
Group by value
To count the number of supermarkets in each city, select the column you want to use for grouping, in this case, city, and place it SELECTafter the . Use the statement after the table name GROUP BYto determine which column you want to use to group the data.
SELECT city, COUNT(*) FROM supermarkets GROUP BY city;
result:
| city | Count |
| ------------- | ----- |
| Los Angeles | 2777 |
| San Francisco | 1024 |
| San Diego | 896 |
The total value for all three cities is 4,697, which count(expression)matches the value in the initial statement.
For reprinting, please send an email to 1244347461@qq.com for approval. After obtaining the author's consent, kindly include the source as a link.
Related Articles
How to select from multiple tables in MySQL
Publish Date：2025/04/25 Views：57 Category：MySQL
-
This article explains how to use MySQL to query from multiple tables in one script SELECT . Let's demonstrate a situation: SELECT name , price, details, type , FROM food, food_order WHERE breakfast.id = 'breakfast_id' Now, let's imagine FRO
Creating a table from CSV in MySQL
Publish Date：2025/04/25 Views：114 Category：MySQL
-
In this article, we aim to understand how to create a table from CSV in MySQL database. Businesses and organizations must quickly generate tables from large amounts of data. These organizations typically have large CSV files with large amou
Creating a Temporary Table in MySQL
Publish Date：2025/04/25 Views：183 Category：MySQL
-
In this article, we aim to explore different ways to create temporary tables in MySQL. One of the main features of temporary tables is that it helps in storing temporary data. This feature is enabled in MySQL 3.23 and later versions. These
Truncate all tables in Mysql
Publish Date：2025/04/25 Views：89 Category：MySQL
-
Today I will show you how to truncate all tables in Mysql. It is used when you want to delete the entire table TRUNCATE TABLE . TRUNCATE It is a type of DML statement, which means it cannot be rolled back once it is committed. There are two
Different ways to check if a row exists in a MySQL table
Publish Date：2025/04/25 Views：163 Category：MySQL
-
This article highlights different ways to check if a row exists in a MySQL table. We will use the EXISTS and NOT EXISTS operators. We can also use these two operators with IF() the function to get a meaningful message if a row (a record) is
Check if table exists in MySQL
Publish Date：2025/04/25 Views：194 Category：MySQL
-
This article provides several options to check if a table exists in MySQL. Before discussing it, let us first see what a table is in MySQL and when you need to check its existence. What is a table in MySQL? A table is a database object that
Rename columns in MySQL database
Publish Date：2025/04/25 Views：80 Category：MySQL
-
In this article, we aim to explore different ways to rename columns in MySQL. ALTER TABLE The command is mainly used to change the format of a given MySQL table. It can be used to add columns, change the data type within a column, delete co
Copying a table in MySQL
Publish Date：2025/04/25 Views：142 Category：MySQL
-
The purpose of this article is to explore different ways to create a copy of a table in MySQL. The source table is also called the table to be copied, and the target table is called the clone table, which can be from the same or a different
Get column names in MySQL
Publish Date：2025/04/25 Views：109 Category：MySQL
-
In this article, we aim to explore how to get the column names of a specific table in MySQL database. Often, when working with data in MySQL, we tend to forget the column names of a particular table in the database and the data types of dif
