intro
Let’s learn everything you need to know about the MySQL COUNT function to get the number of rows in a table or result set.
One of the most common operations when dealing with databases is counting the number of rows in a table or that match a specified condition. To address this need, SQL introduced a special function for counting rows. MySQL has extended that function with additional features, making it even more powerful—but what function are we referring to? The MySQL COUNT function!
In this guide, you will learn what the COUNT MySQL function is, why it is useful, and how and when to use it.
Let’s dive in!
What Is the MySQL COUNT Function?
In MySQL, COUNT is an aggregate function that returns the number of rows retrieved by a SELECT statement. It is typically used to determine the total number of rows in a table, the number of rows that meet specific conditions, or the number of non-NULL values in a column.
COUNT MySQL Function: Syntax and First Examples
The COUNT MySQL function comes with three main variations:
Explore them all!
Counting Values in a Column in Your Table
When applied to an SQL expression, the syntax for the COUNT function is:
1
COUNT(expr)
Where expr is the SQL expression you want to count values for. Generally, expr is a column name. However, it can also be a constant, or a combination of one or more values, operators, and SQL functions that evaluate to a value.
In this case, COUNT returns the number of non-NULL values of expr in the rows retrieved by a SELECT statement. See it in action in the example below:
1
SELECT COUNT(email)
2
FROM employees;
Note that the result is a BIGINT value:
If there are no matching rows, COUNT(expr) returns 0:
1
SELECT COUNT(email)
2
FROM employees
3
WHERE 0;
Since NULL values are ignored, COUNT(NULL) always returns 0:
Counting All Rows in Your Table
The syntax for counting all rows in a MySQL table is as follows:
1
COUNT(*)
COUNT(*) returns the number of all rows retrieved, whether or not they contain NULL values. If the query retrieves no rows, it returns 0.
Usually, this version of the COUNT MySQL function is used to get the total number of rows in a table, as in the following example:
1
SELECT COUNT(*)
2
FROM users;
This will return all records in the users table.
Also, you can harness it in GROUP BY queries to retrieve the number of records in each group:
1
SELECT product_id, COUNT(*) AS total_sales
2
FROM sales
3
GROUP BY product_id;
Counting Unique Rows in Your Table
The MySQL COUNT DISTINCT function has the following syntax:
1
COUNT(DISTINCT expr_1, [expr_2, ..., expr_n])
Where expr_1, [expr_2, ..., expr_n] is a list of one or more expressions separated by commas. Usually, they correspond to column names.
COUNT(DISTINCT expr) returns the number of rows with different non-NULL expr values. Instead, when specifying multiple expressions, it computes the number of unique expression combinations that do not contain NULL.
To better understand how COUNT(DISTINCT expr_1, [expr_2, ..., expr_n]) works, let’s see it in action in an example. Take a look at the employees table below:
Now, suppose you want all unique combinations of (role, age) tuples. You can achieve that with:
1
SELECT COUNT(DISTINCT role, age)
2
FROM employees;
The result will be 6, as the two couples of managers and designers share the same age:
Keep in mind that, if there are no matching rows or combinations, the MySQL COUNT DISTINCT function returns 0.
Note: `COUNT(DISTINCT )` is not allowed and produces an SQL syntax error!*
The MySQL COUNT Function In Different Storage Engines
You might wonder if MySQL stores the total number of rows in a table as part of its metadata, given that this information is frequently requested. Well, this depends on the MySQL storage engine you are using.
MyISAM
MyISAM is not ACID-compliant, but MyISAM is also a storage engine that maintains an exact row count as part of its metadata. That allows for extremely fast COUNT(*) queries since it can simply return the stored value without having to scan the entire table.
InnoDB
InnoDB does not store an exact row count as part of its metadata. That is because storing the exact row count in the metadata is challenging in ACID-based transactional storage engines.
InnoDB supports transactions and row-level locking, where rows can be added, removed, or locked by other transactions. As a result, InnoDB must perform a full table scan to accurately determine the row count, making COUNT(*) queries less efficient compared to MyISAM.
MyISAM vs InnoDB
Note that when a COUNT(*) query includes a WHERE clause, both MyISAM and InnoDB must access the actual table data in the table to apply the condition, which can significantly slow down the query. This is because, regardless of the storage engine, MySQL must evaluate each row against the WHERE clause to determine if it should be included in the count.
So, the MyISAM vs InnoDB COUNT(*) performance comparison can be boiled down to the summary table below:
| Feature | MyISAM | InnoDB |
|---|---|---|
| ACID Compliance | No | Yes |
| Metadata storage for row count | Yes (exact row count stored) | No (exact row count not stored) |
| Locking | Table-level locking | Row-level locking (Table-level locking is permitted) |
COUNT(*) without WHERE clause | Very fast (uses stored row count) | Slower (requires full table scan) |
COUNT(*) with WHERE clause | Slower (must scan data to apply WHERE clause) | Slower (must scan data to apply WHERE clause) |
Advanced Use Cases for COUNT in MySQL
Basic use cases for COUNT in MySQL are pretty intuitive, so let’s dive into more advanced scenarios!
COUNT With WHERE
The COUNT function can be used with the WHERE clause to count rows that meet specific criteria.
For instance, suppose you have a sales table and you want to count the number of sales that occurred in a particular year:
1
SELECT COUNT(*) AS total_sales
2
FROM sales
3
WHERE YEAR(sale_date) = 2024;
The above query will return the total number of sales that occurred in 2024.
COUNT in GROUP BY
Using COUNT with GROUP BY is a great way to count rows for each group of data. This use case is ideal for summarizing and aggregating information across different categories or groups.
Assume you have a users table, and you want to count the number of users in each role:
1
SELECT role, COUNT(*) AS user_count
2
FROM users
3
GROUP BY role;
The query groups the users by their role and counts the number of users in those roles.
COUNT in HAVING
The SQL HAVING clause is employed to filter groups of rows based on aggregate functions like COUNT. That enables you to refine the results of GROUP BY queries by applying conditions to the aggregated data.
Consider that you want to find categories with more than 10 products in your products table. You can achieve that with:
1
SELECT category, COUNT(*) AS product_count
2
FROM products
3
GROUP BY category
4
HAVING COUNT(*) > 10;
In this query, after grouping products by category, the HAVING clause filters the categories to include only those with more than 10 products.
Count Rows in MySQL With DbVisualizer
DbVisualizer is the database client with the highest user satisfaction on the market. That is thanks to its extensive range of features and support for dozens of databases, including MySQL.
One notable feature of DbVisualizer is its ability to count rows in a table or those returned by a query.
Each table in DbVisualizer includes a dedicated “Row Count” tab that displays the number of rows:
Additionally, when you execute a query, DbVisualizer shows the number of returned rows and columns in the bottom right corner:
In DbVisualizer, you can also create a row count monitor to alert you when the number of rows in a table reaches a specific value or changes.
These features make managing MySQL count operations straightforward and visually intuitive with just a few clicks. Evaluate DbVisualizer today!
Conclusion
In this article, you learned what the COUNT function is in MySQL and why it is important. You now understand that the COUNT MySQL function is a powerful tool for counting rows and expression values in result sets.
As shown here, counting rows in MySQL becomes easier with a database client that visually presents information about each table and query. This is precisely what DbVisualizer offers!
In addition to providing common database client features and supporting dozens of DBMSs, DbVisualizer also comes with advanced query optimization capabilities and can generate ERD schemas of your databases with a single click. Download DbVisualizer for free now!
FAQ
What are the performance implications of the MySQL COUNT function?
The performance implications of the MySQL COUNT function change according to the chosen storage engine.
COUNT in InnoDB tables:
COUNT in MyISAM tables:
How to use MySQL COUNT to get unique values?
To count unique values in MySQL, use the COUNT DISTINCT function as in the example below:
1
SELECT COUNT(DISTINCT column_name)
2
FROM table_name;
This query counts the number of distinct values in the specified column.
What is the difference between MySQL COUNT DISTINCT and COUNT?
COUNT and COUNT DISTINCT serve different purposes in MySQL. COUNT(column_name) counts all non-NULL entries in the specified column. In contrast, COUNT(DISTINCT column_name) counts the number of unique, non-NULL values in the column.
For example, if a column has duplicate values, COUNT(column_name) will return the total number of entries, while COUNT(DISTINCT column_name) will return the count of unique values only. Also, the MySQL COUNT DISTINCT function can count tuples.
What is the difference between MySQL DISTINCT and COUNT DISTINCT?
In MySQL, SELECT DISTINCT and COUNT(DISTINCT ...) serve different purposes.
SELECT DISTINCT is used to retrieve unique rows from a query result. When applied to a column or set of columns, it eliminates duplicate values, ensuring that each row in the result set is unique. For example, take a look at the query below:
1
SELECT DISTINCT column_name
2
FROM table_name
This returns all unique values in the specified column. Learn more in our guide on DISTINCT in SQL.
On the other hand, COUNT(DISTINCT ...) is used to count the number of unique values in a column or set of columns. This function helps determine how many distinct entries exist within the specified dataset. For example, check out this sample query:
1
SELECT COUNT(DISTINCT column_name)
2
FROM table_name
That returns the count of unique values in the given column.
A common point of confusion is between SELECT DISTINCT COUNT(*) and SELECT COUNT(DISTINCT ...). SELECT DISTINCT COUNT(*) is generally not useful because COUNT(*) returns the total number of rows, and DISTINCT gets applied to that number. In contrast, SELECT COUNT(DISTINCT column_name) effectively counts how many unique values exist in the specified column.
