Summarizing with COUNT( )
Problem
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.
Solution
Use the COUNT( ) function.
Discussion
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 records in a table, you
could use a SELECT * query, but
to count them instead, use
SELECT
COUNT(*). Without a WHERE
clause, the query counts all the records in the table, such as in the
following query, which 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 query tells you:
mysql> SELECT COUNT(*) FROM states;
+----------+
| COUNT(*) |
+----------+
| 50 |
+----------+COUNT(*) with no
WHERE clause is very quick for ISAM or MyISAM
tables. For BDB or InnoDB tables, you may want to avoid it; the query
requires a full table scan for those table types, which can be slow
for large tables. If an approximate row count is all you require and
you have MySQL 3.23 or later, a workaround that avoids a full scan is
to use SHOW TABLE
STATUS and examine the Rows
value in the output. Were states an InnoDB table,
the query output might look like this:
mysql> SHOW TABLE STATUS FROM cookbook LIKE 'states'\G *************************** 1. row *************************** Name: ...