Create multiple columns on sql

Question

Query written :

CREATE TABLE CEREAL_LIST_TAB
CEREAL_DESC VARCHAR(10)NOT NULL
INSERT INTO CEREAL_LIST_TAB

SELECT 
NVL(CASE WHEN INDICATOR >= '01' AND INDICATOR <= '05' THEN 'WHEAT' 
ELSE CASE WHEN INDICATOR >= '06' AND INDICATOR <= '10' THEN 'RICE' 
ELSE CASE WHEN INDICATOR >= '11' AND INDICATOR <= '15' THEN 'BARLEY' 
ELSE CASE WHEN INDICATOR >= '16' AND INDICATOR <= '20' THEN 'OATS'
ELSE CASE WHEN INDICATOR = '21' AND INDICATOR = '22' THEN 'OTHER' END END END END END,' ')


COUNT (CASE WHEN REVENUE <'1000000' THEN  KEY_FIELD END) as 'Less than $1M',
COUNT (CASE WHEN REVENUE >='1000000' AND REVENUE <='5000000'  THEN KEY_FIELD END) as     '$1M-$5M',
COUNT (CASE WHEN REVENUE >='5000001' AND REVENUE <='10000000'  THEN KEY_FIELD END) as '$5M-$10M',
COUNT (CASE WHEN REVENUE >='10000001' AND REVENUE <='25000000'  THEN KEY_FIELD END) as '$10M-$25M';

FROM TABLE_REVENUE
GROUP BY CEREAL_DESC
ORDER BY CEREAL_DESC;

expected result: I need counts of each cereal in each revenue bucket listed in the query but its's throwing error, please help to let me know where i am making error

Answer 1

Multiple things here,

• You don't need to repeat case expressions you can produce CEREAL_DESC with just one and without NVL
• Correlation names must be coded between ", not '
• you cannot create CEREAL_DESC in the SELECT clause AND use it in the GROUP BY clause, you have to use a subquery or a CTE
• a BETWEEN b and c is more readable to me than a >= b and a <= c
• if a column is numeric compare it to numeric values, else you could trigger unneeded conversions to char types with performance degradations

try this

with
descs as (
SELECT 
  CASE
  WHEN INDICATOR between '01' and '05' THEN 'WHEAT' 
  WHEN INDICATOR between '06' and '10' THEN 'RICE' 
  WHEN INDICATOR between '11' and '15' THEN 'BARLEY' 
  WHEN INDICATOR between '16' and '20' THEN 'OATS' 
  WHEN INDICATOR between '21' and '22' THEN 'OTHER' 
  ELSE '' end as CEREAL_DESC,
  REVENUE
FROM TABLE_REVENUE
)
select
  CEREAL_DESC,
  sum(REVENUE < 1000000) as "Less than $1M",
  sum(REVENUE between 1000001 and 5000000) as "$1M-$5M",
  sum(REVENUE between 5000001 and 10000000) as "$5M-$10M",
  sum(REVENUE between 10000001 and 25000000) as "$10M-$25M"
 from descs
GROUP BY CEREAL_DESC
ORDER BY CEREAL_DESC;