AVG with OVER - invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [closed]

Question

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?

Answer 1

Either remove the group by clause or apply the avg function without an window or as someone else suggested, add an extra aggregate function that satisfies the group by clause:

a)

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

b)

SELECT date, Day_Name_of_week
     , AVG(SalesItems_Breakfast) BreakfastAvg
FROM day_Sales_total
GROUP BY date, Day_Name_of_week

c) see @Serg's answer

I believe alternative b) is the way to go here

Answer 2

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.