-> (SELECT producer_name, years AS months FROM producer)

| AVG(albums) |
+-------------+
| 2.0000 |
+-------------+
1 row inset sec)
You can see that the inner query joins together artist and album, and groups the albums together by artist so you can get a count for each artist. If you run it in isolation, here’s what happens:
mysql> SELECT COUNT) AS albums FROM artist INNER JOIN album
-> USING (artist_id) GROUP BY artist.artist_id;
+--------+
| albums |
+--------+
| 7 |
| 1 |
| 2 |
| 1 |
| 1 |
| 1 |
| 1 |
+--------+
7 rows inset sec)
Now, the outer query takes these counts—which are aliased as albums
—and averages them to give the final result. This query is the typical way that you apply two aggregate functions to one set of data. You can’t apply aggregate functions in cascade, as in
AVG(COUNT(*))
; it won’t work:
mysql> SELECT AVG(COUNT(*)) FROM album INNER JOIN artist
-> USING (artist_id) GROUP BY artist.artist_id;
ERROR 1111 (HY000): Invalid use of group function
With subqueries in
FROM
clauses, you can return a scalar value, a set of column values,
more than one row, or even a whole table. However, you can’t use correlated subqu- eries, meaning that you can’t reference tables or columns from tables that aren’t explicitly listed in the subquery. Note also that you must alias the whole subquery using the
AS
keyword and give it a name, even if you don’t use that name anywhere in the query.
User Variables
Often you’ll want to save values that are returned from queries. You might want to do this so that you can easily use a value in a later query. You might also simply want to save a result for later display. In both cases, user variables solve the problem they allow you to store a result and use it later.
Let’s illustrate user variables with a simple example. The following query finds the name of an artist and saves the result in a user variable:

Download 4.24 Mb.

Share with your friends: