You want to count the number of rows in a table, the number of rows that match certain conditions, or the number of times that particular values occur.
To count the number of rows in an entire table or that match
particular conditions, use the COUNT()
function. For example, to
display the contents of the rows in a table, you can use a SELECT
*
statement, but to count them instead, use SELECT
COUNT(*)
. Without a WHERE
clause, the statement counts all the
rows in the table, such as in the following statement that shows how
many rows the driver_log
table
contains:
mysql>SELECT COUNT(*) FROM driver_log;
+----------+
| COUNT(*) |
+----------+
| 10 |
+----------+
If you don’t know how many U.S. states there are, this statement tells you:
mysql>SELECT COUNT(*) FROM states;
+----------+
| COUNT(*) |
+----------+
| 50 |
+----------+
COUNT(*)
with noWHERE
clause is very
quick for MyISAM tables. However, for BDB or InnoDB tables, you may want to avoid it because the
statement requires a full table scan, which can be slow for large
tables. If an approximate row count is all you require, a workaround
that avoids a full scan for those storage engines is to extract
theTABLE_ROWS
value
from the INFORMATION_SCHEMA
database:
mysql>SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES
->WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'states';
+------------+ | TABLE_ROWS | +------------+ | 50 | +------------+
Before MySQL 5.0, INFORMATION_SCHEMA
is unavailable. Instead,
use SHOW
TABLE
STATUS
and extract the value of the Rows
column.
To count only the number of rows that match certain conditions,
include an appropriate WHERE
clause
in a SELECT
COUNT(*)
statement. The conditions can be
chosen to make COUNT(*)
useful for
answering many kinds of questions:
How many times did drivers travel more than 200 miles in a day?
mysql>SELECT COUNT(*) FROM driver_log WHERE miles > 200;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
How many days did Suzi drive?
mysql>SELECT COUNT(*) FROM driver_log WHERE name = 'Suzi';
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
How many states did the United States consist of at the beginning of the 20th century?
mysql>SELECT COUNT(*) FROM states WHERE statehood < '1900-01-01';
+----------+
| COUNT(*) |
+----------+
| 45 |
+----------+
How many of those states joined the Union in the 19th century?
mysql>SELECT COUNT(*) FROM states
->WHERE statehood BETWEEN '1800-01-01' AND '1899-12-31';
+----------+ | COUNT(*) | +----------+ | 29 | +----------+
The COUNT()
function
actually has two forms. The form we’ve been using, COUNT(*)
, counts rows. The other form,
COUNT(
expr
)
, takes a column name or expression
argument and counts the number of non-NULL
values. The following statement shows
how to produce both a row count for a table and a count of the number
of non-NULL
values in one of its
columns:
SELECT COUNT(*), COUNT(mycol) FROM mytbl;
The fact that COUNT(
expr
)
doesn’t count NULL
values is useful for producing multiple
counts from the same set of rows. To count the number of Saturday and
Sunday trips in the driver_log
table with a single statement, do this:
mysql>SELECT
->COUNT(IF(DAYOFWEEK(trav_date)=7,1,NULL)) AS 'Saturday trips',
->COUNT(IF(DAYOFWEEK(trav_date)=1,1,NULL)) AS 'Sunday trips'
->FROM driver_log;
+----------------+--------------+ | Saturday trips | Sunday trips | +----------------+--------------+ | 3 | 1 | +----------------+--------------+
Or to count weekend versus weekday trips, do this:
mysql>SELECT
->COUNT(IF(DAYOFWEEK(trav_date) IN (1,7),1,NULL)) AS 'weekend trips',
->COUNT(IF(DAYOFWEEK(trav_date) IN (1,7),NULL,1)) AS 'weekday trips'
->FROM driver_log;
+---------------+---------------+ | weekend trips | weekday trips | +---------------+---------------+ | 4 | 6 | +---------------+---------------+
The IF()
expressions determine, for each column value, whether it should be
counted. If so, the expression evaluates to 1
and COUNT()
counts it. If not, the
expression evaluates to NULL
and
COUNT()
ignores it. The
effect is to count the number of values that satisfy the condition
given as the first argument to IF()
.
Summaries and NULL Values further discusses the
difference between COUNT(*)
and
COUNT(
expr
)
.
18.219.103.183