Member Avatar for SheSaidImaPregy

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.
Thank you.

  • 2 Contributors
  • 4 Replies
  • 79 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by SheSaidImaPregy
Member Avatar for katharnakh

I dont know whether i completely understand the need, but let me give it a try...

i take the problem like this.
- there are albums
- each album has photos
- i want to take album details based on the maximum views of photos, is that right?

if so,

select AlbumLabel, AlbumID, DateCreated, AlbumDate, AlbumLocation, AlbumEvent from Albums where AlbumID in ( 
select distinct(AlbumID) from Photos order by sum(views) desc limit 4 )

I am not sure whether order by sum(views) this will work with inner query.


let me know how it works...


all the best,
kath

Member Avatar for SheSaidImaPregy

oh no sorry it doesn't work. This is how it's set up:

2 tables, one deals with albums and is relational to the photos table only by AlbumID.
In the photos table, each photo has a "View" that is updated when it is viewed. I want to pull the "hottest albums" by suming up all the photo views each album has, and ordering from highest to lowest.

so, I need to take all the info from the Albums that I need for the page, then I need to order that info based on the views in the photos table.

Understand? Thanks for the help. The query works, but does not order the records by the Sum like it should.

EDITED: Oh you should know, LIMIT does not work in "IN" statements in sql. IM on mysql 5.0.41 or something.

Member Avatar for SheSaidImaPregy

if anyone can please help...

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

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.