Database Reference
In-Depth Information
As you learned in Chapter 12, “Summarizing Data,” aggregate functions are
used to summarize data. Although all the examples of aggregate functions thus
far summarized data from a single table only, these functions can also be used
with joins.
To demonstrate this, let's look at an example. You want to retrieve a list of all
customers and the number of orders that each has placed. The following code
uses the
COUNT()
function to achieve this:
▼
Input
SELECT customers.cust_name,
customers.cust_id,
COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
▼
Output
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc. | 10001 | 2 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+---------+
▼
Analysis
This
SELECT
statement uses
INNER JOIN
to relate the
customers
and
orders
tables to each other. The
GROUP BY
clause groups the data by cus-
tomer, and so the function call
COUNT(orders.order_num)
counts the
number of orders for each customer and returns it as
num_ord
.
Aggregate functions can be used just as easily with other join types. See the fol-
lowing example:
▼
Input
SELECT customers.cust_name,
customers.cust_id,
COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;
