How to use the results of a query to filter a table comparing one field to the result?

I want to filter a table showing only the rows where total is between ± 3 standard deviations from the average.

The query I’m using is this:

   SELECT * 
      FROM AccountsDatabase
      WHERE total > (AVG(total) + STDEVP(total) * -3) 
      AND total < (AVG(total) + STDEVP(total) * +3)`

But I’m getting the error:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Answer

Try something like this. I have added some sample data as your question has not given any data and schemas. You need to use group by clause when you use Aggregate functions in our queries. I suggest you to refer to Group by and aggregate functions in SQL server.

Sample data scripts:

  SELECT * 
     INTO #SRC_Data
     FROM
      (
        SELECT 0 AS A, 8.0 AS B,1 AS C UNION ALL
        SELECT 1, 8.0,7 UNION ALL
        SELECT 2, 6.0,3 UNION ALL
        SELECT 3,9.0,4 UNION ALL
        SELECT 4, 10.0,6 UNION ALL
        SELECT 5, 11.0,2 

      ) AS A

1st way: You need to use GROUP BY and HAVING when use Aggregate functions in filters. Having can filter by groups.

    SELECT *        
    FROM #SRC_Data
    GROUP BY  A, B, C
    HAVING A >= (AVG(A) + STDEVP(A) * -3) 
        AND A < (AVG(A) + STDEVP(A) * 3)

2nd way: Use Aggregations as a sub query. in that case you don’t need to use group by or having as you use subquery in where clause.

   SELECT * 
    FROM #SRC_Data
       WHERE A > ( SELECT AVG(B) FROM #SRC_Data ) +  ( SELECT STDEVP(B)  * -3 FROM #SRC_Data) 
            AND A <  (SELECT AVG(B) FROM #SRC_Data ) +  ( SELECT STDEVP(B) * 3 FROM #SRC_Data)

     DROP TABLE #SRC_Data