Stack Exchange
SQL AVG in subselect
I want to get rates from a product (productId) that is in a specific category (fk_rate_category) and the overall average rates from all products in the category.
To be more specific: I need the data for a chart. One line in the chart represents the rate of a product(y-axis = rate ; x-axis = date_added) and the other line shows the average rates of all products.
I had the following approach:
SELECT        id, productId, rate, fk_rate_category, date_added,
                             (SELECT        AVG(rate) AS Expr1
                               FROM            rates
                               WHERE        (fk_rate_category = r.fk_rate_category)) AS avgRate
FROM            rates AS r
WHERE        (productId = @productId) AND (fk_rate_category = @fk_rate_category)

The problem is, that with my approach the avgRates value is the same in every record returned. 
Any  idea?

Where does FK_Cateogry_ID appear? Is this in the rates table?

sorry. i mean fk_rate_category of course. I edited the initial posting.

Firstly, always try to avoid using a correlated subquery when you can achieve the same result with a join, the optimiser will deal with it much better and you'll get the results faster.
I think the problem could be because you are not linking the dates of the data as well as FK_Rate_Cateogry, I'd imagine if this is for graphical purposes you would want the average by date:
SELECT  r.ID,
        r.ProductID,
        r.Rate,
        r.FK_Rate_Category,
        r.Date_Added,
        ar.avgRate
FROM    Rates r
        LEFT JOIN
        (   SELECT  r.Date_Added, 
                    r.FK_Rate_Category, 
                    AVG(r.Rate) AS avgRate
            FROM    Rates r
            GROUP BY r.FK_Rate_Category, r.Date_Added
        ) ar
            ON ar.FK_Rate_Category = r.FK_Rate_Category
            AND ar.Date_Added = r.Date_Added
WHERE   r.ProductID = @ProductID
AND     r.FK_Rate_Category = @FK_Rate_Category;


iam sorry, i had a typo in my intial posting. there is no FK_Cateogry_ID i meant fk_rate_category

I get a "wrong syntax near ar" error message when pasting the code into visual studio. any idea what could be wrong?

I've edited this now to remove reference to the FK_Category_ID issue as this is clearly not it. I've also corrected my inability to spell category... Hopefully the syntax in the amended version is now correct. If not could you post some sample data and expected output. http://www.sqlfiddle.com/ is an ideal place for this.

Any decent DBMS optimizer will rewrite a co-related sub-query with a JOIN anyway (if it's possible - it's not always the same thing). There is no need to avoid them.

You got the set of records which have the specific id and rate_category. And avgRate is an average value from all records with the same rate_category - this set has the outer one as subset(see conditions of both queries). So all returned records have the same avgRate value. 
So I see some holes in your logic line.
