Database Reference
In-Depth Information
This chapter uses other tables occasionally as well. You can create them with scripts
found in the
tables
directory of the
recipes
distribution.
Recipe 5.12
describes the
kjv
table.
8.1. Basic Summary Techniques
Problem
You want to summarize a dataset in various ways, such as counting the number of rows
that match certain conditions, determining the smallest or largest of a set of values,
adding or averaging a set of numbers, or finding which unique values are present.
Solution
Use the appropriate aggregate function to summarize values,
DISTINCT
to select unique
values, or
COUNT(DISTINCT)
to count unique values.
Discussion
The following discussion illustrates how to apply the aggregate functions to produce
basic summaries, and how to use
DISTINCT
to find unique values.
Summarizing with COUNT()
To count the number of rows in an entire table or that match particular conditions, use
the
COUNT()
function. For example, to display the rows in a table, use a
SELECT
*
state‐
ment, 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 US states there are (perhaps you think there are 57?), this
statement tells you:
mysql>
SELECT COUNT(*) FROM states;
+----------+
| COUNT(*) |
+----------+
| 50 |
+----------+
