MySQL COUNT Function Examples

The MySQL databases contain massive amounts of data stored in the form of tables and the database administrator wants to know the count of records present in a table or that matches a specific condition to make reports or do analysis. The MySQL count() function is utilized to count the number of records from a table, and it can be used with multiple operations and conditions.

Before beginning with this post, make sure that you log in to your MySQL Server and use the database in which you want to test the working of the count function.

Count Rows in MySQL

The “count” function is utilized to count the number of rows in the MySQL table, and to count all the rows in a table, use the “*” wildcard character that represents select all. The syntax for it is given below:

SELECT COUNT(*) FROM [table-name];

For example, if you want to count all numbers of rows that exist in the table “OrderItem,” run this command:

SELECT COUNT(*) FROM OrderItem;

The output displays the total number of rows that exists in the table “OrderItem”:

Counting the Number of Distinct Values in a Column of MySQL Table

The “count” function has the ability to count all the distinct values in a column of a MySQL table by using the “distinct” clause using this syntax:

SELECT COUNT(DISTINCT [column-name]) FROM [table-name];

For example, if the user wants to count the distinct values from the column “FirstName” from the table “Customer” use this command:

SELECT COUNT(DISTINCT FirstName) FROM Customer;

The output displays the number of distinct values in column “FirstName”:

Counting the Number of Rows in Multiple Tables

The “count” function has the ability to count the total number of rows in multiple tables using the syntax:

SELECT COUNT(*) FROM [table1-name], [table2-name], [table3-name];

Let’s see an example of counting the rows of tables “Customer”, “Supplier” and “Product”, so run this command:

SELECT COUNT(*) FROM Customer, Supplier, Product;

The output is displaying total numbers of rows in multiple tables:

Counting the Number of Rows in a Table That Meet a Defined Condition

The “where” clause aids in defining a condition, it can be used in the “count” function, to count the number of rows in a table that meet a defined condition. The syntax is given below:

SELECT COUNT(*) FROM [table-name] WHERE [column-name] = 'value';

let’s take an example to count the numbers of rows from the table “Product” where the “UnitPrice” is greater than and equal to “15”, run this command:

SELECT COUNT(*) FROM Product WHERE UnitPrice >= 15;

The count is displaying the number of rows that match the given condition:

Counting the Number of Rows in a Table That Meets Multiple Conditions

The “count” function can count the number of rows that fulfill multiple conditions and these multiple conditions can be defined in a “where” clause using the logical operators “or” and “and”. For this, use this syntax:

SELECT COUNT(*) FROM [table-name] WHERE [column1-name] = 'value' AND [column2-name] = 'value';

For example, if the user wants to count the numbers of rows from the “OrderItem” that fulfill both conditions that “UnitPrice” is equal to “20” and “Quantity” is equal to “20”. Run the given below command:

SELECT COUNT(*) FROM OrderItem WHERE UnitPrice = 20 AND Quantity = 10;

The output displays the numbers of rows that match both conditions:

Counting the Number of Rows Based on Range of Values

The “count” function can count the numbers of rows on a condition based on the range of values using the “between” and “and” operator, using this syntax:

SELECT COUNT(*) FROM [table-name] WHERE [column-name] BETWEEN 1 AND 10;

For example, if the user wants to count numbers of rows in the “Product” table, where the “SupplierId” in between the range of “1” to “10”, run this command:

SELECT COUNT(*) FROM Product WHERE SupplierId BETWEEN 1 AND 10;

The output is showing the count of numbers where the “SupplierId” is in the defined range:

Counting the Number of Rows in a Table That Match a Defined Pattern

To find a specific pattern in the record, a simple “like” keyword is added along with the “count” operator. The syntax for this is as follows:

SELECT COUNT(*) FROM [table-name] WHERE [column-name] LIKE 'pattern%';

For example, if the user wants to count the number of rows in the table “Supplier” in the “Country” column if it matches a pattern having “%UK%” and the “%” represents that zero, single or multiple characters. Run this command:

SELECT COUNT(*) FROM Supplier WHERE Country LIKE '%UK%';

The output displays the count that matches a defined pattern:

Counting the Number of Rows Based on Null Values

The “count” function can use “is null” operator to count the number of rows in a table that does not have any value, using this syntax:

SELECT COUNT(*) FROM [table-name] WHERE [column-name] IS NULL;

Let’s see an example if the user wants to count all the numbers of rows that does not have any value in the “employee” table, by running the given below command:

SELECT COUNT(*) FROM employee WHERE Country IS NULL;

The output successfully counts the number of rows based on Null values:

You have successfully learned about the count function along with different examples.

Conclusion

The “count” function in MySQL is used to count the number of rows in a table. It can also be used to count the number of rows from multiple tables, or that meet a specific pattern or condition. Users can also count the number of rows with multiple conditions. This post demonstrated the MySQL count function and its examples.