Oracle SQL Throwing "ORA-00934: group function is not allowed here" error for simple Select w/AVG I am having a hard time with what should be a simple query. I have studied the documentation on the AVG function and understand that I may need to execute this  with a subquery. This is the gist of what I want to accomplish - finding values greater than 3 * the average of the non-zero values of a column. Here is what I thought would work. Any help appreciated.

SELECT COL1, COL2
FROM TABLE1
HAVING COL3 &gt; 0 AND COL3 &gt; AVG(COL3*3)
ORDER BY COL1;
For that version of AVG(), you need to GROUP BY on the columns, but that way your average value would be incorrect. Sure you can use a subquery (where you do not reference any other column, and because of that, you do not need a GROUP BY inside), for example:

SELECT COL1, COL2
FROM TABLE1
WHERE COL3 &gt; (SELECT AVG(COL3) * 3 FROM TABLE1)
ORDER BY COL1;


But this will perform 2 index/table scans, that work can be reduced by using the analytic version of AVG():

SELECT * FROM
(
  SELECT COL1, COL2, COL3, AVG(COL3) OVER () * 3 AS COL3_AVG3
  FROM TABLE1
)
WHERE COL3 &gt; COL3_AVG3;


Edit: forgot about the greater than zero condition, including that:

SELECT COL1, COL2
FROM TABLE1
WHERE COL3 &gt; (SELECT AVG(COL3) * 3 FROM TABLE1 WHERE COL3 &gt; 0)
ORDER BY COL1;


And:

SELECT * FROM
(
  SELECT COL1, COL2, COL3, SUM(CASE WHEN COL3 &gt; 0 THEN COL3 ELSE 0 END) OVER () / SUM(CASE WHEN COL3 &gt; 0 THEN 1 ELSE 0 END) OVER () * 3 AS COL3_AVG3
  FROM TABLE1
)
WHERE COL3 &gt; COL3_AVG3;