Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SELECT average values FROM another table

Author  Topic 

smcallister
Starting Member

15 Posts

Posted - 2008-05-19 : 15:18:35
So I have this table called "listings"... there are 100 unique listings with an integer ID for each.

I have another table called "ratings"... in there are multiple entries that have a listing_id field and a rating field. The rating field is a value from 0-10.

I want to select ALL "listings" from the listings table... and then sort based on the average number that the multiple rating fields in the ratings table has for that listing.

I CAN NOT figure it out!! Any help would be greatly appreciated. Please respond if I have not explained this properly. Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 15:21:35
select l.id, l.listid
from listings as l
left join (select listid, avg(rating) as Yak from ratings group by listid) as r on r.listid = l.listid
order by r.yak



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

smcallister
Starting Member

15 Posts

Posted - 2008-05-19 : 15:45:44
OK, this is putting me on the right track but I can't seem to convert it into what I need. I will give some more specifics about my table.

TABLE_LISTINGS
column: ID
column: name
column: style

TABLE_REVIEWS
column: listingID
column: rating


I want to SELECT * from TABLE_LISTINGS where STYLE = 2.

Then, for each ID in TABLE LISTINGS, I want to SELECT * from TABLE_REVIEWS where listingID matches ID.. and I want to take the average rating of the returned columns.

Then I want to SORT the TABLE_LISTINGS based on the results of those averages from TABLE_REVIEWS.

Thanks.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 15:53:35
Here it is s-p-e-l-l-e-d o-u-t for you.

select l.id, l.name
from table_listings as l
left join (select listingid, avg(rating) as Yak from table_reviews group by listingid) as r on r.listingid = l.id
where l.style = 2
order by r.yak



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

smcallister
Starting Member

15 Posts

Posted - 2008-05-19 : 16:02:56
THANKS!!!! I GOT IT WORKING!!!

What should I use to call the value of "Yak" for each listing? I'm using PHP
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 02:07:44
[code]select l.id,
l.name,
r.Yak AS MyAlias
from table_listings as l
left join (
select listingid,
avg(rating) as Yak
from table_reviews
group by listingid
) as r on r.listingid = l.id
where l.style = 2
order by r.yak[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

smcallister
Starting Member

15 Posts

Posted - 2008-05-20 : 09:57:52
peso, thank you very much!! you have not only fixed this problem, but have also progressed my understanding of SQL into a whole new avenue. I have thanked you in my code and when my little project is complete, I will forward you the link!! Much appreciated!!!
Go to Top of Page
   

- Advertisement -