Search code examples
sqlsumconditional-statementscase

sql conditional sum statement

The sql conditional sum statement below keeps throwing me an error and I'm not sure why. The logic should create a flag when any of the underlying flags are 1, thus the sum >=1.

Error message: mismatched nput '(' expecting <EOF>

,case when (sum(case when (TAB_flag = 1) OR
(TOP_SPOT_flag = 1) OR
(PAGE_flag = 1) OR
(DOWNLOAD_flag = 1) >= 1)) THEN 1 ELSE 0 END AS any_p_flag
Solution

  • You have two case expressions. You need to enter then/else to your inner case expression. You may not need an inner case expression and just sum your fields, I can't tell without a sample of the data. The below should work:

    ,case when (sum(
  case when (TAB_flag = 1) OR
  (TOP_SPOT_flag = 1) OR
  (PAGE_flag = 1) OR
  (DOWNLOAD_flag = 1) then 1 else 0 end 
>= 1)
) THEN 1 ELSE 0 END AS any_p_flag

    If your flag fields contain 1 or 0 and you are looking for any 1 you could do this:

    ,case when 
sum(TAB_flag + TOP_SPOT_flag + PAGE_flag + DOWNLOAD_flag)  > 0
THEN 1 ELSE 0 END AS any_p_flag