Member Avatar for SheSaidImaPregy

Hello, I am trying to set up a string where it taps two databases, Albums and Photos. I need to select the following columns of Albums database and Photos where the Albums.AlbumType='Nightlife' and then order by the sum of photos.views desc for ranking the top 4 (limit 4)

This is what I have but, obviously does not work:

"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"

Now, in the Photos database, each photo has a photoid, albumid to match the album, and a views column. I need to basically add all the views together to get a sum for each albumid then rank the top 4. Then pull the album information for displaying. Been working on this awhile and can't seem to get it to work. Thanks.

  • 1 Contributor
  • 2 Replies
  • 76 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by SheSaidImaPregy
Member Avatar for SheSaidImaPregy

oh, and I am very new to the row_number() method, so if I could put the row number of each one on the site (ranked #1, #2, #3, #4) without having to use a separate function, that would be great.

Member Avatar for SheSaidImaPregy

Nevermind, figured it out. Thanks anyway:

"SELECT Albums.AlbumLabel, Albums.AlbumID, Albums.DateCreated, Albums.AlbumDate, Albums.AlbumLocation, Albums.AlbumEvent, SUM(Photos.Views) FROM Albums, Photos WHERE Albums.AlbumType='Nightlife' and Albums.AlbumID=Photos.AlbumID GROUP BY Photos.AlbumID ORDER BY SUM(Photos.Views) DESC LIMIT 4"
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.