CODEWITHSUNDEEP
sqlsql-servert-sql
Sukhjeevan
Sukhjeevan

Reputation: 3156

T-SQL help needed regarding CASE statement in SELECT clause

What is best approach

Approach-I

SELECT 
SUM(CASE WHEN CODE = 'A' THEN ISNULL(UNIT,0.00)+ISNULL(UNIT_1,0.00) END) AS UNIT_SUM
FROM tblA

Approach-II

SELECT 
ISNULL(SUM(CASE WHEN CODE = 'A' THEN UNIT+UNIT_1 END),0.00) AS UNIT_SUM
FROM tblA

1) my concern is Do i have to place ISNULL inside CASE statement OR outsite CASE statement. Does it affect summation of units OR both query give same result. And what happen if any UNIT column have NULL value.Does it result NULL of total.

2) do i must use ELSE in CASE statement as given below:

THEN ... ELSE 0.00 END

Upvotes: 1

Views: 1707

Answers (6)

peterm
peterm

Reputation: 92785

Answer to the original question:

Do I have to place ISNULL inside CASE statement...?

No you don't. SUM() ignores NULLs. Therefore the presence of NULL values won't affect the result of summation. The exception is when all values are NULL, or you don't have any values at all. In that case you'll get NULL as a result.

Do I must use ELSE in CASE statement..?

No you don't for the same reasons.

...Do I need to use ISNULL() outside CASE..?

It depends on what you expect in the resultset in case when all values are NULLs or you don't have any values. If you want to have 0 or any other value then use ISNULL() or COALESCE(). Otherwise don't.

Here is SQLFiddle demo

UPDATE

Given the expression like one in your changed question

SUM(CASE WHEN CODE = 'A' THEN UNIT + UNIT_1 END)

you most likely want to use ISNULL() or COALESCE() around both UNIT and UNIT_1 if you want to treat an absent value of one of these columns as zero values. In that case if you want to get a zero in a resultset even if there are no values that match your CASE condition you still need to use ISNULL() or COALESCE() around SUM().

SELECT ISNULL(SUM(CASE WHEN CODE = 'A' 
                       THEN ISNULL(UNIT, 0) + ISNULL(UNIT_1, 0) 
                  END), 0) AS UNIT_SUM

or

SELECT COALESCE(SUM(CASE WHEN CODE = 'A' 
                         THEN COALESCE(UNIT, 0) + COALESCE(UNIT_1, 0) 
                    END), 0) AS UNIT_SUM

Here is SQLFiddle demo

Upvotes: 1

roman
roman

Reputation: 117345

You have other answers pointing out that you better put your filter = 'A' into where clause to speed up the query, and that's right thing to do. I also want to note that your queries produce different results in case there's no rows to sum:

SELECT 
ISNULL(SUM(CASE WHEN CODE = 'A' THEN UNIT END),0.00) AS UNIT_SUM
FROM tblA;
-- output: 0

SELECT 
SUM(CASE WHEN CODE = 'A' THEN ISNULL(UNIT,0.00) END) AS UNIT_SUM
FROM tblA;
-- output: null

If you want to get sum of all your rows and and put this data into columns, you can use this query:

select
    isnull(sum(case when code = 'A' then unit end), 0) as [A],
    isnull(sum(case when code = 'B' then unit end), 0) as [B]
from tblA
-- filter out rows if there's more codes in your table
where code in ('A', 'B')

sql fiddle demo

Upvotes: 2

dani herrera
dani herrera

Reputation: 51655

Either, the right approach is:

SELECT 
ISNULL(SUM(UNIT),0.00) AS UNIT_SUM
FROM tblA
WHERE CODE = 'A'    --<------------ Index friendly

Your approach is bad because it is not index friendly. Also, with case statement, you should evaluate each row.

If you have more columns to aggregate you can perform a CTE query then join results:

;with cte_a as (
    SELECT 
    coalesce(SUM(UNIT),0.00) AS UNIT_SUM_A
    FROM tblA
    WHERE CODE = 'A'
),
cte_b as (
    SELECT 
    coalesce(SUM(UNIT),0.00) AS UNIT_SUM_B
    FROM tblA
    WHERE CODE = 'B'
)
SELECT UNIT_SUM_A, UNIT_SUM_B
FROM cte_a cross join cte_b b

Edited second query by @RomanPekar comment.

Upvotes: 1

dnoeth
dnoeth

Reputation: 60462

Both variations return the same result:

The 1st replaces NULLs with zeroes before the SUM and the 2nd replaces a NULL after the SUM, i.e. there's no row with 'A'.

Btw, i would replace proprietary ISNULL with a Standard SQL COALESCE(UNIT,0.00).

And of course you could simply replace it with:

CASE WHEN CODE = 'A' THEN UNIT ELSE 0.00 END

Upvotes: 0

Tom Chantler
Tom Chantler

Reputation: 14931

If you just want the sum for cases where CODE='A' , then I would would write it like this:

SELECT ISNULL(SUM(UNIT),0.00) AS UNIT_SUM
FROM tblA
WHERE CODE = 'A'

If you are selecting other columns too, then I'd do it like this:

SELECT ISNULL(SUM(CASE WHEN CODE = 'A' THEN UNIT ELSE 0.00 END)) AS UNIT_SUM
FROM tblA

Upvotes: 1

athabaska
athabaska

Reputation: 455

If you use approach-II you dont need ELSE, so I guess in terms of having less code its better.

Upvotes: 0

Related Questions