Error Case and Group by - Oracle SQL Developer

Question

Hello, I’m trying to group the results of a query with case as follows:

select 
    case
      when sal BETWEEN 0 and 100000 then 1
      when sal BETWEEN 100001 and 200000 then 2
      when sal BETWEEN 200001 and 300000 then 3
      when sal BETWEEN 300001 and 400000 then 4
    else 5 END AS "ESCALAO",
    count(*) AS "QTD"
FROM EMP
GROUP BY ESCALAO
ORDER BY QTD DESC;

But without success. The error message that says:

ORA-00904: "SCALAO": invalid identifier 00904. 00000 - "%s: invalid Dentifier" *Cause:
*Action: Line error: 10 Column: 10

What’s wrong with the query?

Answer 1

You cannot use the name of a column nicknamed as at Group By. Try this:

select ESCALAO,COUNT(*) AS QTD
from
(
    select 
        case
          when sal BETWEEN 0 and 100000 then 1
          when sal BETWEEN 100001 and 200000 then 2
          when sal BETWEEN 200001 and 300000 then 3
          when sal BETWEEN 300001 and 400000 then 4
          else 5 END AS 'ESCALAO'
    FROM EMP
)
GROUP BY ESCALAO
ORDER BY QTD DESC;