Recipe 8.1. Summarizing with COUNT( )ProblemYou 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. SolutionUse the COUNT( ) function. DiscussionTo 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 no WHERE 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 the TABLE_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:
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( ).
See AlsoSection 8.8 further discusses the difference between COUNT(*) and COUNT( expr ). |