GROUP BY after CASE WHEN

Question

I am trying to create a table from a join and summarize some fields based on id. This part works great. I am also trying to add an extra column and use the case where the statement I want to populate.

Here is the script

CREATE TABLE TABLE1 AS SELECT ID, IDC, SUM(AMOUNT) PRICE, SUM(COST) COST, SUM(AMOUNT-COST) PROFIT, CASE PROFIT WHEN PROFIT < 1000 THEN 'Low' WHEN PROFIT < 5000 THEN 'Medium' ELSE 'High' END AS PROFITLEVEL FROM (SELECT DISTINCT ID, IDC, AMOUNT, COST FROM ORDER_ITEMS LEFT JOIN ORDERS ON ID = IDC) GROUP BY ID, IDC; 

This returns ORA-00905: Keyword Missing Error.

Any help would be appreciated

Answer 1

You are using CASE incorrectly; also, you are trying to use the PROFIT alias at the same level that you define it.

You need to edit CASE and use an expression that gives PROFIT instead of the alias PROFIT :

 CREATE TABLE TABLE1 AS SELECT ID, IDC, SUM(AMOUNT) PRICE, SUM(COST) COST, SUM(AMOUNT - COST) PROFIT, CASE WHEN SUM(AMOUNT - COST) < 1000 THEN 'Low' WHEN SUM(AMOUNT - COST) < 5000 THEN 'Medium' ELSE 'High' END AS PROFITLEVEL FROM (SELECT DISTINCT ID, IDC, AMOUNT, COST FROM ORDER_ITEMS LEFT JOIN ORDERS ON ID = IDC) GROUP BY ID, IDC; 

The way you tried to use CASE is useful if you need to check single values; eg:

 select level, case level when 1 then 'one' when 2 then 'two' else 'other' end from dual connect by level <=3