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?