Summary: in this tutorial, you will learn how to use the MySQL COUNT function to count the number rows in a table.
Introduction to the MySQL COUNT function
The COUNT function returns the number of rows in a table. The COUNT function allows you to count all rows in a table or rows that match a particular condition.
The syntax of the COUNT function is as follows.
COUNT(expression)
The return type of the COUNT function is BIGINT. The COUNT function returns zero if there was no matching row found.
There are several forms of the COUNT function: COUNT(*), COUNT(expression) and COUNT(DISTINCT expression).
MySQL COUNT(*)
The COUNT(*) function returns the number of rows in a result set returned by a SELECT statement. The COUNT(*) function counts rows that contain no-NULL and NULL values.
If you use the COUNT(*) function to count the number rows in a table without using the WHERE clause and selecting other columns, it will perform very fast.
This optimization is applied to MyISAM tables only because the number of rows of a MyISAM table is stored in the table_rows column in the tables table of the information_schema database; therefore MySQL can retrieve it very quickly.
MySQL COUNT(expression)
The COUNT(expression) returns the number of rows that do not contain NULL values.
MySQL COUNT(DISTINCT expression)
The COUNT(DISTINCT expression) returns the number of unique rows that dot not contain NULL values.
MySQL COUNT examples
Let’s create a new table named demos and insert some sample data for the demonstration.
-- create a demos table CREATE TABLE IF NOT EXISTS demos( id int auto_increment primary key, val int ); -- insert some sample data INSERT INTO demos(val) VALUES(1),(1),(2),(2),(NULL),(3),(4),(NULL),(5); -- select data from demos table SELECT * FROM demos;
To count all rows in the demos table, you use the COUNT(*) function as follows:
SELECT COUNT(*) FROM demos;
You can add a WHERE clause to specify a condition to count e.g., to count only rows whose val column contains number 2, you use the following query:
SELECT COUNT(*) FROM demos WHERE val = 2;
If you specify the val column in the COUNT function, the COUNT function counts all rows whose val column contains non-NULL values only. See the following query:
SELECT COUNT(*) FROM demos WHERE val = 2;
Two NULL values in the val column are ignored.
To count unique rows in the demos table, you add the DISTINCT operator to the COUNT function as the following query:
SELECT COUNT(DISTINCT val) FROM demos;
Two duplicate values 1,2 and two NULL values are ignored in the counting.
MySQL COUNT with GROUP BY
We often use the COUNT function in conjunction with GROUP BY clause to characterize the data in various groups. See the following products table.
For example, to find how many products in each product line, you use the COUNT function with the GROUP BY clause as follows:
SELECT productline, count(*) FROM products GROUP BY productline;
To find the number of products supplied by vendors, you use the following query:
SELECT productvendor, count(*) FROM products GROUP BY productvendor;
To find which vendor supplies at least 9 products, you use the COUNT function in the HAVING clause as the following query:
SELECT productvendor, count(*) FROM products GROUP BY productvendor HAVING count(*) >= 9
MySQL COUNT IF
You can use a control flow function e.g., IF, IFNULL, CASE, etc., in the COUNT function to count rows whose values match a condition.
For example, the following query finds how many orders are cancelled, on hold and disputed:
SELECT COUNT(IF(status='Cancelled',1, NULL)) 'Cancelled', COUNT(IF(status='On Hold',1, NULL)) 'On Hold', COUNT(IF(status='Disputed',1, NULL)) 'Disputed' FROM orders;
The IF function returns 1 if the order’s status is cancelled, on hold or disputed, otherwise it returns NULL. The COUNTfunction only counts 1, not NULL values therefore the query returns the number of orders based on the corresponding status.
In this tutorial, you have learned various techniques to count the number of rows in a table using the MySQL COUNTfunction.
随时随地看视频
