Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- \!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.
Add Comment
Please, Sign In to add comment