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+]