Database Reference
In-Depth Information
13
In this chapter, you learn how to group data so you can summarize subsets of table
contents. This involves two new
SELECT
statement clauses: the
GROUP BY
clause
and the
HAVING
clause.
In the last chapter, you learned that the SQL aggregate functions can be used
to summarize data. This enables you to count rows, calculate sums and aver-
ages, and obtain high and low values without having to retrieve all the data.
All the calculations thus far were performed on all the data in a table or on data
that matched a specific
WHERE
clause. As a reminder, the following example
returns the number of products offered by vendor
1003
:
▼
Input
SELECT COUNT(*) AS num_prods
FROM products
WHERE vend_id = 1003;
▼
Output
+-----------+
| num_prods |
+-----------+
| 7 |
+-----------+
But what if you want to return the number of products offered by each ven-
dor? Or products offered by vendors who offer a single product, or only those
who offer more than ten products?
This is where groups come into play. Grouping enables you to divide data into
logical sets so you can perform aggregate calculations on each group.
