SQL case in select query.

Question

I have following query

Select
        a,b,c,
        case totalcount
            when 0 then 0
            else abcd/totalcount
        end AS 'd',
        case totalcount
            when 0 then 0
            else defg/totalcount
        end AS 'e'
    From 
        Table1

In this query, I have same case statement in select query...Can i make it into single select statement.

"totalcount" is some value... abcd and defg are two column values from table1. If totalcount is zero, i dont want to divide, else I wish to divide to get an average value of abcd and defg.

Answer 1

At first view, I would say no, since you seem to use the exact same condition leading to different results.

Besides, this looks odd to me, why would you need two SELECT CASE for only one condition? This makes no sense.

Could you be more specific or give a real-world example of what you're trying to ask, with "real" data so that we might better answer your question?

EDIT #1

Given that:

Yes...it's two different fields....and the valud i need is calculated one...which is Value = a/b. But i need to check if b is zero or not

I would still answer no, as if they are two different fields, and you want both results in your result set, then you will need to write two CASE statements, one for each of the fields you need to verify whether it is zero-valued or not. Keep in mind that one CASE statement is equivalent to one single column only. So, if you need to check for a zero value, you are required to check for both independently.

By the way, sorry for my misunderstanding, that must be a language barrier, unfortunately. =) But my requirement for "real" data example holds, since this might light us all up with some other related solutions, we never know! =)

EDIT #2

Considering you have to check for a value of 0, I would perhaps rewrite my code as follows, for the sake of readability:

select a, b, c
        , case when condition1 <> 0 then abcd / condition1 else 0 end as Result1
        , case when condition2 <> 0 then defg / condition2 else 0 end as Result2
    from Table1

In my opinion, it is leaner and swifter in the code, and it is easier to understand what is the intention. But after all, the result would be the same! No offense made here! =)

EDIT #3

After having read your question edit:

"totalcount" is some value... abcd and defg are two column values from table1. If totalcount is zero, i dont want to divide, else I wish to divide to get an average value of abcd and defg.

I say, if it is the average that you're after, why not just use the AVG function which will deal with it internaly, without having to care about zero values?

select a, b, c
        , AVG(abcd) as Result1
        , AVG(defg) as Result2
    from Table1

Plus, considering having no record, the average can only be 0!

I don't know about your database engine, but if it is Oracle or SQL Server, and I think DB2 as well, they support this function.

Answer 2

You need to elaborate more - what is the condition? With what you have entered so far, it looks like you are just using the result of the expression, making the CASE statement redundant.

For example, see my inline comments:

SELECT a,b,c,
    case condition
        when 0 then 0        --this is unnecessary
        else abcd/condition, --this is just using the result of the condition?
    case condition
        when 0 then 0        --this is unnecessary
        else defg/condition  --this is just using the result of the condition?
from Table1

so you could just refactor this as:

SELECT  a
       ,b
       ,c
       ,expression
       ,expression 
from Table1

if you actually meant the else abcd/condition as evaluate another condition or use a default value then you need to expand on that so we can answer accurately.

EDIT: if you are looking to avoid a divide by zero and you only want to evaluate condition once, then you need to do it outside of the SELECT and use the variable inside the SELECT. I wouldn't be concerned about the performance impact of evaluating it multiple times, if the condition doesn't change then its value should be cached by the query execution engine. Although it could look ugly and unmaintainable, in which case you could possibly factor condition into a function.

Answer 3

No, since you seem to be requiring two output columns in your desired resultset... Each Case statement can generate at most one output value.

It doesn't matter how many columns the case statement needs to access to do it's job, or how many different values it has to select from (how many When... Then... expressions), it just matters how many columns you are trying to generate in the final resultset.

Answer 4

If all you're trying to do is avoid dividing by zero, and if it's not critical that an attempt to divide by zero actually produces zero, then you can do the following:

Select
    a,b,c,
    abcd / NULLIF(totalcount,0) AS 'd',
    defg / NULLIF(totalcount,0) AS 'e'
From 
    Table1

This transforms an attempt to divide by zero into an attempt to divide by NULL, which always produces NULL.

If you need zero and you know that abcd and defg will never be NULL, then you can wrap the whole thing in an ISNULL(...,0) to force it to be zero. If abcd or defg might legitimately be null and you need the zero output if totalcount is zero, then I don't know any other option than using a CASE statement, sorry.

Moreover, since it appears totalcount is an expression, for maintainability you could compute it in a subquery, such as:

Select
    a,b,c,
    abcd / NULLIF(totalcount,0) AS 'd',
    defg / NULLIF(totalcount,0) AS 'e'
From 
(
    Select
        a,b,c,
        abcd,
        defg,
        (... expression goes here ...) AS totalcount
    From 
        Table1
) Data

But if it's a simple expression this may be overkill.

Answer 5

Technically this is possible. I'm not suggesting it's a good idea though!

;with Table1 as
(
select 1 as a, 2 as b, 3 as c, 0 as condition, 1234 as abcd, 4567 AS defg UNION ALL
select 1 as a, 2 as b, 3 as c, 1 as condition, 1234.0 as abcd, 4567 AS defg 
)
,
cte AS
(
Select
a,b,c,
case condition
    when 0 then CAST(CAST(0 as decimal(18, 6)) as binary(9)) + CAST(CAST(0 as decimal(18, 6)) as binary(9))
    else CAST(CAST(abcd/condition as decimal(18, 6)) as binary(9)) + CAST(CAST(defg/condition as decimal(18, 6)) as binary(9))
end AS d
From 
Table1
)
Select
     a,
     b,
     c, 
     cast(substring(d,1,9) as decimal(18, 6)) as d, 
     cast(substring(d,10,9) as decimal(18, 6)) as e
From cte