Search code examples
sqlcase

Is there any way to write a case statement in SQL, if column A > 0 then column B, column C= 0

I have multiple columns which have the same value and it is showing incorrect results when I summarize them. Is there any way to write a case statement in SQL, if column A > 0 then make all remaining columns be 0 (column B, column C, column D = 0) ?

If column B > 0, then make all remaining columns be 0 (column A, column C, column D = 0).

1st column gets the higher priority in this sequence.

enter image description here

enter image description here

I tried writing case statement but it did not work.

Solution

  • See if this example helps:

    SELECT a
    ,CASE WHEN a > 0 THEN 0 ELSE b END AS b
    ,CASE WHEN a > 0 OR b > 0 THEN 0 ELSE c END AS c
    ,CASE WHEN a > 0 OR b > 0 OR c > 0 THEN 0 ELSE d END AS d
FROM tbl;