oracle - SQL - explanation on PIVOT behavior

Question

For the cost column values from a table course, try to pivot into a table of how many times a cost values happens, e.g $1095 happens 3 times.

      1095       1195       1595       NULL
---------- ---------- ---------- ----------
         3         25          1          1

Cost values

select cost from course
order by cost;

COST
----------
1095
1095
1095
1195
...
1195
1595
NULL

This works.

WITH counter AS (
    SELECT 
        cost
    FROM course    
)
select * from counter
pivot (
    count(*)
    for (cost)
    in (1095, 1195, 1595, NULL)
);

      1095       1195       1595       NULL
---------- ---------- ---------- ----------
         3         25          1          1

This does not.

select cost from course
pivot (
    count(*)
    for (cost)
    in (1095, 1195, 1595, NULL)
);

ORA-00904: "COST": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 178 Column: 8

Question

It seems to me both selecting cost and doing count(*) but the former worked but the latter did not. Please help understand why.

question from:https://stackoverflow.com/questions/65870160/sql-explanation-on-pivot-behavior

Answer 1

• select cost from course doesn't work

  SQL> select cost from course
    2  pivot (
    3      count(*)
    4      for (cost)
    5      in (1095, 1195, 1595, NULL)
    6  );
  select cost from course
         *
  ERROR at line 1:
  ORA-00904: "COST": invalid identifier
  

  See the asterisk? Points to error cause.

• select * from course works:

  SQL> select * from course
    2  pivot (
    3      count(*)
    4      for (cost)
    5      in (1095, 1195, 1595, NULL)
    6  );
  
        1095       1195       1595       NULL
  ---------- ---------- ---------- ----------
           2          1          1          0
  

Reason? That's the syntax:

SELECT * FROM                  --> here; there's no "COLUMN1" or "<column list>" but "*"
(
  SELECT column1, column2
  FROM tables
  WHERE conditions
)
PIVOT 
(
  aggregate_function(column2)
  FOR column2
  IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];