I am pulling a simple query but basing it off of a group of sums. What I want to do is pull all the information from "albums" where albumID equals "photos" albumID. Then Grouping by Photos.albumID, I want to grab the sum of all the views of each albumid, and arrange it that way desc with only 4 records.
Basically, I want to order 4 album records by the sum of their photo's views. This is what I have and it does not work the right way. Can anyone lead me in the right direction?
"SELECT Albums.AlbumLabel, Albums.AlbumID, Albums.DateCreated, Albums.AlbumDate, Albums.AlbumLocation, Albums.AlbumEvent FROM Albums, Photos WHERE Albums.AlbumType='Nightlife' ORDER BY SUM(Photos.Views) DESC LIMIT 4"
By adding AND Photos.AlbumID=Albums.AlbumID I get my 4 records but all duplicates.