Database Reference
In-Depth Information
COUNT(*)
with no
WHERE
clause performs a full table scan. For MyISAM tables, this is
very quick. For InnoDB tables, you may want to avoid it because it can be slow for large
tables. If an approximate row count is good enough, avoid a full scan by extracting the
TABLE_ROWS
value from the
INFORMATION_SCHEMA
database:
SELECT
TABLE_ROWS
FROM
INFORMATION_SCHEMA
.
TABLES
WHERE
TABLE_SCHEMA
=
'cookbook'
AND
TABLE_NAME
=
'states'
;
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 of the United 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 argu‐
ment 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:
