Oracle SQL Throwing "ORA-00934: group function is not allowed here" error for simple Select w/AVG
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;