•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 402,860 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,898 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser: Programming Forums
Views: 756 | Replies: 4
 |
•
•
Join Date: Sep 2007
Posts: 1,057
Reputation: 
Rep Power: 3
Solved Threads: 61
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?
By adding AND Photos.AlbumID=Albums.AlbumID I get my 4 records but all duplicates.
Thank you.
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"
Thank you.
Last edited by SheSaidImaPregy : Oct 26th, 2007 at 9:59 am.
•
•
Join Date: Jan 2006
Posts: 218
Reputation:
Rep Power: 3
Solved Threads: 19
Posting Whiz in Training
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,
I am not sure whether
let me know how it works...
all the best,
kath
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
Last edited by katharnakh : Oct 26th, 2007 at 11:34 am.
challenge the limits
•
•
Join Date: Sep 2007
Posts: 1,057
Reputation:
Rep Power: 3
Solved Threads: 61
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.
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.
Last edited by SheSaidImaPregy : Oct 26th, 2007 at 1:36 pm.
•
•
Join Date: Sep 2007
Posts: 1,057
Reputation:
Rep Power: 3
Solved Threads: 61
if anyone can please help...
This is what I have but, obviously does not work:
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.
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"
•
•
Join Date: Sep 2007
Posts: 1,057
Reputation:
Rep Power: 3
Solved Threads: 61
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"
|
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
Similar Threads
- Removing Query Strings (ASP.NET)
- Double MySQL Query (PHP)
- Dynamic Query (JSP)
- MySQL nested query / joined query conversion help (MySQL)
- problem with lengthy query (Java)
- Retreiving variables from a sql query into a form (PHP)
- Sql Query (VB.NET)
- Query Building (Database Design)
Other Threads in the MySQL Forum
- Previous Thread: mysql search and display data help....
- Next Thread: UK postcodes
