Let’s take a look at grouping data. For example, we know how to find out the average year of all of the albums we purchased, but what about the same information for a single artist?
We can select out only the data we’re interested in by using a where clause in our select statement:
select min(year), max(year), avg(year) from albums where artist="Alice Cooper"
But what if we want to see all artists in our list? That’s what the group by clause is for. We can group our data by a field or set of fields. In this case, let’s group by artist.
select artist, min(year), max(year), count(album) from albums group by artist
Our Abba albums came out between 1974 and 1980. Our Alice Cooper albums came out between 1971 and 2003, and there were 22 of them.
Who do we have the most albums of? We can give any function a name and than sort by that name.
select artist, count(album) as albumcount
from albums group by artist order by albumcount desc, artist
Alice Cooper and Elton John top the list at 22 and 21 albums, respectively. That “desc” means descending. By default, sorting goes from least to greatest. When we specify desc it will go from greatest to least. In this case, it puts the artists with the most albums on top of the list.
Related Pages
