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…