Case and Group by Error - Oracle SQL Developer

Question

Case and Group by Error - Oracle SQL Developer

Navigation

#1 by (1 votes)

1

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: "SCALE": Invalid identifier   00904. 00000 - "% s: invalid identifier"   * Cause:
  * Action:   Error on line: 10 Column: 10

What is wrong with the query?

sql oracle pl-sql sqldeveloper

asked by anonymous 07.12.2015 / 18:25

1 answer

How to use Firebird database on the network with C #? Download file via ashx

score 1 · Accepted Answer

You can not use the name of a column named as in 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;