AVG with OVER - invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause I have what I believe to be a correctly written sql query that calculates the average using an over clause to window the results. However, I'm getting an error when I include the over clause.

SELECT date,
        Day_Name_of_week,
        AVG(SalesItems_Breakfast) OVER (PARTITION BY Day_Name_of_week) AS BreakfastAvg
    FROM day_Sales_total
    GROUP BY date, day_Sales_total.Day_Name_of_week


Which gives me the following error:


  Msg 8120, Level 16, State 1, Line 93
  Column 'day_Sales_total.SalesItems_Breakfast' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Which is bizarre, because as SalesItems_Breakfast is inside the AVG function.

However, the below query runs fine, the only difference being the over clause has been removed:

SELECT date,
        Day_Name_of_week,
        AVG(SalesItems_Breakfast) AS a --OVER (PARTITION BY Day_Name_of_week) AS BreakfastAvg
    FROM day_Sales_total
    GROUP BY date, day_Sales_total.Day_Name_of_week


What's going on?
Probably you need 

SELECT date,
        Day_Name_of_week,
        AVG(AVG(SalesItems_Breakfast)) OVER (PARTITION BY Day_Name_of_week) AS BreakfastAvg
    FROM day_Sales_total
    GROUP BY date, day_Sales_total.Day_Name_of_week


So that GROUPed BY date, day_Sales_total.Day_Name_of_week average AVG(SalesItems_Breakfast) is partitoned by Day_Name_of_week and new AVG() is calculated.