CODEWITHSUNDEEP
sqlconditional-statementscase
P3P0
P3P0

Reputation: 165

Multiple condition in CASE

i want to create a query that can select multiple conditions using CASE. I have the below query that sum when number of windows it's more than 0 but whe I combined to create a "Windows_multiple" doesn't work. It's multiple when have more than one type of windows.

Select
SUM(CASE when h.number_of_windows_b > 0 then h.hh_weight end)/15 as Windowsb_only,
SUM(case when h.number_of_windows_c > 0 then h.hh_weight end)/15 as Windowsc_only,
SUM(case when h.number_of_windows_o > 0 then h.hh_weight end)/15 as Windowso_only,

The code above works but when I'm trying to create a query that sum h.number of windows when are multiple I'm not getting the expected output. I'm using the below query:

SUM(CASE when h.number_of_windows_b > 0 AND (h.number_of_windows_c > 0 OR  h.number_of_windows_o > 0) 
         then h.hh_weight )/15
         when h.number_of_windows_c  > 0 AND (h.number_of_windows_b > 0 OR  h.number_of_windows_o > 0) 
         then h.hh_weight )/15
         when h.number_of_windows_o  > 0 AND (h.number_of_windows_b > 0 OR  h.number_of_windows_c > 0) 
         then h.hh_weight end)/15 as Windows_Multiple

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

This answers the original version of the question.

You might just have premature ends:

SUM(CASE when h.number_of_windows_b > 0 AND (h.number_of_windows_c > 0 OR  h.number_of_windows_o > 0) 
         then h.hh_weight / 15
         when h.number_of_windows_c  > 0 AND (h.number_of_windows_b > 0 OR  h.number_of_windows_o > 0) 
         then h.hh_weight / 15
         when h.number_of_windows_o  > 0 AND (h.number_of_windows_b > 0 OR  h.number_of_windows_c > 0) 
         then h.hh_weight / 15
     END) as Windows_Multiple

Upvotes: 1

Related Questions