Oracle SQL Throwing “ORA-00934: group function is not allowed here” error for simple Select w/AVG

oracle

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 > 0 AND COL3 > AVG(COL3*3)
ORDER BY COL1;

Best Answer

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 > (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 > COL3_AVG3;

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

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

And:

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