CODEWITHSUNDEEP
sqlsql-server
Numbaker
Numbaker

Reputation: 81

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

Upvotes: 0

Views: 1349

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

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 *.

Upvotes: 1

JediSQL
JediSQL

Reputation: 21

For SQL Server, write the query that computes the averages in a CTE (Common Table Expression). Then filter by average in the main query that uses the CTE.

WITH FooBar as (
  SELECT  A.*,
  AvgF = (select AVG(X1.parm) from (
    VALUES (A.F1),(A.F2),(A.F3)) as X1(parm)
    WHERE X1.parm > 0
    AND   X1.parm is not NULL
  )
  FROM    MyTable A
)
SELECT  *
FROM    FooBar
WHERE   AvgF > 10

Upvotes: 1

Andrew
Andrew

Reputation: 7880

I think something like this should do the trick:

SELECT TOP 5 *
FROM (
  SELECT F1, F2, F3, (F1+F2+F3)/3 AS Average
  FROM Table
) Subquery
WHERE Average > 10
ORDER BY Average DESC;

AVG works across records, not across columns, so you need to manually calculate it.

Remember to do an ORDER BY in descending order if you really want the top records.

Upvotes: 1

Related Questions