Use of SQL GROUP BY

I wrote this query for use on my site beta.killtheheart.com

When you click on the hotlist tab you will see the query

version 1: Problems… i was including the date in my query using a group by

<cfquery datasource=“#variables.dsn#” name=“qHotList”>

SELECT   l.artistID, l.AlbumID, l.Date, count(l.ArtistID) as recordCount, art.Artist, alb.Title

FROM tblLogger l, tblArtists art, tblAlbums alb

WHERE l.ArtistID=art.ArtistID

AND l.AlbumID=alb.AlbumID

AND Date

BETWEEN ’10/15/06′ and ’10/18/06′

GROUP BY l.ArtistID,l.AlbumID, l.Date, art.Artist, alb.Title

ORDER BY recordCount DESC

</cfquery>

the result of this would be for instance

==============================================

Artist – Album

#1. 311 – Greatest Hits

#2. 311 – Greatest Hits

#3. 311 – Greatest Hits

#4. A.F.I – Decemberunderground

#5. A.F.I – Decemberunderground

#6. A.F.I – Decemberunderground

==============================================

I couldn’t understand why I was getting multiple rows? It was very confusing to me… I asked my boss who runs (Mark Kruger!) and he informed me what was going on in the query and the reason i was getting multiple entries (1 for each day that had gone by) was because of my “date” column.

Here is version 2:

<cfquery datasource=“#variables.dsn#” name=“qHotList”>

SELECT   l.artistID, l.AlbumID, count(l.ArtistID) as recordCount, art.Artist, alb.Title

FROM tblLogger l, tblArtists art, tblAlbums alb

WHERE l.ArtistID=art.ArtistID

AND l.AlbumID=alb.AlbumID

AND Date

BETWEEN ’10/15/06′ and ’10/18/06′

GROUP BY l.ArtistID,l.AlbumID, art.Artist, alb.Title

ORDER BY recordCount DESC

</cfquery>

the result of this would be for instance

==============================================

Artist – Album

#1. 311 – Greatest Hits

#2. A.F.I – Decemberunderground

==============================================

And now the query works giving me a hot list of albums and artists that people have chosen on my site…

Leave a comment