Recipe 8.1. Summarizing with COUNT ( )

Recipe 8.1. 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 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:

• 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⁠(⁠ ⁠ ⁠).

Create a View to Simplify Using a Summary

If you need a given summary often, a view might be useful so that you need not type the summarizing expressions repeatedly. For example, the following view implements the weekend versus weekday trip summary:

mysql> CREATE VIEW trip_summary_view AS     -> 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;                

Selecting from this view is much easier than selecting directly from the underlying table:

mysql> SELECT * FROM trip_summary_view; +---------------+---------------+ | weekend_trips | weekday_trips | +---------------+---------------+ |             4 |             6 | +---------------+---------------+ 

See Also

Section 8.8 further discusses the difference between COUNT(*) and COUNT( expr ).