0

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?

  • 3
    By adding a PARTITION BY you're creating a further sub-grouping of results, so the AVG value then has to be included in the GROUP BY since it is a separate grouping in the result set. I'd suggest reading the Documentation for a better understanding of the function. – LowlyDBA Jul 7 '16 at 15:49
  • You do not need a window function for this simple aggregation - simply drop the OVER clause, because it partitions by the same criteria as that of your grouping. – mustaccio Jul 7 '16 at 15:53
  • @mustaccio no. The group by is by 2 columns,. The partition by is by one of them only. – ypercubeᵀᴹ Jul 7 '16 at 16:12
  • Yeah, the window clause was unnecessary, yesterday was a long day, I clearly had lost my ability to write sql! – Neil P Jul 8 '16 at 7:42
2

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

|improve this answer|||||
4

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.

|improve this answer|||||
  • Or AVG(SUM(SalesItems_Breakfast)) or AVG(MIN(SalesItems_Breakfast)), ... or some other combination. – ypercubeᵀᴹ Jul 7 '16 at 16:10

Not the answer you're looking for? Browse other questions tagged or ask your own question.