select top 10 with the highest average score

Question

Lets say, I have Product and Score tables.

Product
-------
id
name

Score
-----
id
ProductId
ScoreValue

I want to get the top 10 Products with the highest AVERAGE scores, how do I get the average and select the top 10 products in one select statement?

here is mine which selects unexpected rows

SELECT TOP 10 Product.ProductName Score.Score 
FROM Product, Score 
WHERE Product.ID  IN (select top 100  productid  
                       from score 
                       group by productid 
                       order by sum(score) desc) 
order by Score.Score desc

Answer 1

This might do it

SELECT TOP 10 p.ProductName, avg( s.Score ) as avg_score
FROM Product p
    inner join Score s on s.product_id = p.product_id
group by p.ProductName, p.product_id
order by avg_score desc