Databases Reference
In-Depth Information
| AVG(albums) |
+-------------+
| 2.0000 |
+-------------+
1 row in set (0.00 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 in set (0.00 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 ex-
plicitly 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:
