Cacher is the code snippet organizer for pro developers

\!h Example - convert years to months in producer list:
SELECT producer_name, months FROM -- returns producer_name and months from the subquery
-- subquery returns two columns, producer_name and months(alias for years multiplied by 12), from the producer table
(SELECT producer_name, years*12 AS months FROM producer) AS prod; -- 2nd alias here needs to be included
+---------------+--------+
| producer_name | months |
+---------------+--------+
| Phil Spector  |    432 |
| George Martin |    480 |
| Tina Weymouth |    240 |
| Chris Frantz  |    240 |
| Ed Kuepper    |    180 |
+---------------+--------+

\!h Example 2 - average number of albums we own by each artist:
-- avg of 'albums' subquery
SELECT AVG(albums) FROM 
-- subquery returns no. of unique albums and aliases it 'albums'
(SELECT COUNT(*) AS albums FROM artist INNER JOIN album
USING (artist_id) GROUP BY artist.artist_id) AS alb;
+-------------+
| AVG(albums) |
+-------------+
|      2.1667 |
+-------------+
\!h These subqueries are a good way to apply two aggregate functions to one set of data
-- This is because things like AVG(COUNT(*)) wont work.