SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SELECT average values FROM another table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smcallister
Starting Member

15 Posts

Posted - 05/19/2008 :  15:18:35  Show Profile  Visit smcallister's Homepage  Reply with Quote
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

Sweden
30114 Posts

Posted - 05/19/2008 :  15:21:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 05/19/2008 :  15:45:44  Show Profile  Visit smcallister's Homepage  Reply with Quote
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

Sweden
30114 Posts

Posted - 05/19/2008 :  15:53:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 05/19/2008 :  16:02:56  Show Profile  Visit smcallister's Homepage  Reply with Quote
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

Sweden
30114 Posts

Posted - 05/20/2008 :  02:07:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



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

smcallister
Starting Member

15 Posts

Posted - 05/20/2008 :  09:57:52  Show Profile  Visit smcallister's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000