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

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?

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

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.