Column invalid in select list even though it's in aggregate

Question

Here is my query:

SELECT ScriptName
      ,BranchName
      ,AVG(Passes) OVER (PARTITION BY ScriptName, BranchName) AS AvgPasses
FROM temp3
GROUP BY ScriptName, BranchName

Here is my error:

Column 'temp3.Passes' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I understand GROUP BY. Does the OVER PARTITION BY mean it won't work with GROUP BY? I haven't been able to find anything on MSDN. If it does mean it won't work, can someone explain why? I cannot think of a circumstance in which a problem would arise from this.

Answer 1

Yes, the over partition by means that it will not work with group by. So remove it:

SELECT ScriptName, BranchName,
       AVG(Passes) AS AvgPasses
FROM temp3
GROUP BY ScriptName, BranchName;

Technically, the reason is because Passes is not in the group by and not in an aggregation function.

First, if Passes is an integer, the average will also be an integer. If you want a floating point average, then convert the value to the appropriate type.

Second, analytic functions do work with group by. For instance, if you wanted the sum of all the averages, you could do:

SELECT ScriptName, BranchName,
       AVG(Passes) AS AvgPasses,
       SUM(AVG(Passes)) over (ScriptName, BranchName) as SumAvg
FROM temp3
GROUP BY ScriptName, BranchName;

Ok, that seems weird. But the min() and max() might be useful.