CODEWITHSUNDEEP
sqlselectsumcase
Matthew
Matthew

Reputation: 43

Using CASE(AND) combined with CASE into a singlar grouping

I have a dataset where I am using a SELECT CASE to combine sources into new groupings which for this exercise I am calling income_type. The data I have is:

dataset

     SOURCE       DOLLARS    BONUS  
 -------------- ----------- ------- 
  RADIO A             1902       0  
  RADIO B            21093      12  
  RADIO C            19203   35432  
  SATELLITE             93       0  
  LOCAL TV            9321       0  
  CABLE TV             903       0  
  SECONDARY TV        3902       0  
  RADIO C           594032    3902  
  RADIO B            40983     190  
  CABLE TV           18930       0  
  LOCAL TV           44032       0  
  RADIO C         38932098     390  
  SATELLITE           3083       0  
  LOCAL TV       203983209       0  
  CABLE TV        32329329       0  
  SECONDARY TV        8230       0  
  RADIO B              349       0  
  RADIO C               90       0

I am using a CASE to group into either Radio or TV. 

SELECT CASE source
WHEN 'RADIO A' THEN 'RADIO'
WHEN 'RADIO B' THEN 'RADIO'
WHEN 'RADIO C' THEN 'RADIO'
WHEN 'SATELLITE' THEN 'TV'
WHEN 'LOCAL TV' THEN 'TV'
WHEN 'CABLE TV' THEN 'TV'
WHEN 'SECONDARY TV' THEN 'TV'
ELSE 'OTHER' END AS income_type

This works as planned. I am now trying to use a CASE AND within that CASE to create new groups within Radio because within radio there is now 'Hit Radio' and 'Standard Radio' which will need to be summed later down the road. Hit Radio is defined by having BONUS > 0. Where BONUS is 0 then it is Standard Radio. Only Radio can receive bonus'. My current query is...but isn't working:

SELECT CASE source
WHEN 'RADIO A' AND BONUS > 0 THEN 'HIT RADIO'
WHEN 'RADIO A' AND BONUS = 0 THEN 'STANDARD RADIO'
WHEN 'RADIO B' AND BONUS > 0 THEN 'HIT RADIO'
WHEN 'RADIO B' AND BONUS = 0 THEN 'STANDARD RADIO'
WHEN 'RADIO C' AND BONUS > 0 THEN 'HIT RADIO'
WHEN 'RADIO C' AND BONUS = 0 THEN 'STANDARD RADIO'
WHEN 'SATELLITE' THEN 'TV'
WHEN 'LOCAL TV' THEN 'TV'
WHEN 'CABLE TV' THEN 'TV'
WHEN 'SECONDARY TV' THEN 'TV'
ELSE 'OTHER' END AS income_type

Any help would be greatly appreciated!

Upvotes: 0

Views: 39

Answers (1)

larsen
larsen

Reputation: 1431

I suggest to use a different form for your case statement, for example: 

  CASE
    …
    WHEN source = 'RADIO C' AND bonus = 0 THEN 'standard radio'
    WHEN source IN ('SATELLITE', 'LOCAL TV', 'CABLE TV', …) THEN 'TV
    ELSE 'OTHER' 
  END AS income_type

As you can see you can drop the column name source at the beginning of the CASE clause, and have more freedom to express cases in its body (where you can then use normal SQL operators, such as IN).

HTH

Upvotes: 1

Related Questions