Use average of values in “where” clause

I want to select the top x value from a query where the average of valid values for a particular field is above 10. Why is this throwing an error?

SELECT TOP 5 *
FROM Table A
WHERE AVG(X1.parm) from (VALUES (A.F1),(A.F2),(A.F3)) as X1(parm) where X1.parm >0 and X1.parm is not NULL) > 10

Answer

Your code is throwing an error because avg() is an aggregation function and cannot be in the where clause.

What you want seem to want is apply:

SELECT TOP 5 *
FROM Table A CROSS APPLY
     (SELECT AVG(X1.parm) as avg_parm
      FROM (VALUES (A.F1), (A.F2), (A.F3)) X1(parm) 
      WHERE X1.parm > 0
     ) a
WHERE avg_parm > 10;

The subquery calculates the average. That calculation can be used in the WHERE clause.

Note that the comparison to NULL is unnecessary, because > filters out NULL values.

Also, this returns the average. If you don’t want the average in the results, just select A.* instead of *.