Thanks to iTunes, sqlTunes, SQL Server and my inner nerd(*), I can report definitively that my top three favourite music album purchases of 2011 were:
- Songs Lost and Stolen (2011) – Bella Hardy
- Angel Station (1979) – Manfred Mann’s Earth Band
- Punching Air In the Nyah EP (2010) – Back Back Forward Punch
(*) who am I kidding with “inner nerd” – I’m nerd all the way to the surface. Here’s the SQL that gave me the results – note the use of various keywords that I added to track comments in iTunes in order to exclude singles, partial albums, or bonus tracks from the computation of ratings.
select album, artist, AVG(Rating) as Rating, COUNT(*) as TrackCount
from Track T
where
ISNULL(Genre, '') <> 'Classical'
AND ISNULL(Genre, '') <> 'Podcast'
AND isnull(Comments, '') NOT like '%Single%'
AND isnull(Comments, '') NOT like '%PartialAlbum%'
AND isnull(Comments, '') NOT like '%BonusTrack%'
AND Album is not null
and (select MAX([Date Added]) from track T1
where T1.Album = T.Album and T1.[Date Added]
between '1 jan 2011' and '31 dec 2011') is not null
group by album, artist
having count(*) > 1
order by AVG(Rating) desc
[This content was originally posted to Google+]