GROUP BY after CASE WHEN

Question

I am trying to create a table from a join and summing some fields based on id. This part is working great. I am also trying to add an additional column and using a case when statement I want to populate it.

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 however returns a ORA-00905 : Missing keyword error.

Any help would be appreciated

Answer 1

You are using the CASE in a wrong way; besides, you try to use the alias PROFIT at the same level you define it.

You need to edit you CASE and use the expression that gives the 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 the CASE is useful if you need to check single values; for example:

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