Reuse Oracle SQL subquery

Question

I'm trying to list all items that have cost > average cost. This query works:

SELECT description, cost, (SELECT AVG(cost) FROM course) as average_cost FROM course 
WHERE cost > (SELECT AVG(cost) FROM course);

but I'd like to reuse

(SELECT AVG(cost) FROM course)

I've tried using WITH like some suggested (example). However it doesn't work.

WITH avg_cost AS (SELECT AVG(cost) FROM course)
SELECT description, cost, avg_cost as average_cost FROM course 
WHERE cost > avg_cost;

or

WITH avg_cost AS (SELECT AVG(cost) as avg_cost FROM course)
SELECT description, cost, avg_cost.avg_cost as average_cost FROM course 
WHERE cost > avg_cost.avg_cost;

It looks like avg_cost is a table itself.

Answer 1

CTE (or a subquery) it is, but - not the way you put it. You have to name column within the CTE, and then use the CTEs name in JOIN.

Something like this:

with temp as 
  (select round(avg(cost), 2) as avg_cost from course)
select c.description, c.cost, t.avg_cost
from course c join temp t on c.cost > t.avg_cost;

---

Illustration (sample data from Scott's EMP table):

SQL> select round(avg(cost), 2) avg_cost from course;

  AVG_COST
----------
   2073,21

SQL> select * from course order by cost desc;

DESCRIPTIO       COST
---------- ----------
KING             5000    --> King to Clark have their COST
FORD             3000        larger than AVG(COST) from the 
SCOTT            3000        whole table
JONES            2975
BLAKE            2850
CLARK            2450     -->
ALLEN            1600
TURNER           1500
MILLER           1300
WARD             1250
MARTIN           1250
ADAMS            1100
JAMES             950
SMITH             800

14 rows selected.

Query:

SQL> with temp as
  2    (select round(avg(cost), 2) as avg_cost from course)
  3  select c.description, c.cost, t.avg_cost
  4  from course c join temp t on c.cost > t.avg_cost
  5  order by c.cost desc;

DESCRIPTIO       COST   AVG_COST
---------- ---------- ----------
KING             5000    2073,21        --> As expected ... King to Clark
FORD             3000    2073,21
SCOTT            3000    2073,21
JONES            2975    2073,21
BLAKE            2850    2073,21
CLARK            2450    2073,21

6 rows selected.

SQL>