The column is not valid in the selection list, although it is collectively

Question

Here is my request:

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

Here is my mistake:

The column "temp3.Passes" is not valid in the select list because it is not contained in the aggregate function or in the GROUP BY clause.

I get it GROUP BY. Does it mean OVER PARTITION BYthat it will not work with GROUP BY? I could not find anything on MSDN. If this means that this will not work, can someone explain why? I cannot think of circumstances due to which a problem would arise.

Answer 1

Yes, it over partition bymeans that it will not work with group by. So delete it:

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

, Passes group by, .

-, Passes - , . , .

-, group by. , , :

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

, . min() max().