Multiple columns in a single CASE expression

Question

I am sure this has been reviewed many times, so please forgive my repetition. I have a query that works, but currently has 6 CASE statements in a single selection. Someone mentioned that it is best to optimize by putting all my WHEN conditions in one CASE. However, I cannot achieve this.

select right(RTRIM(region),5) as cell_id, sum(CASE WHEN LEFT(cparty,3) in ('999','998','997') THEN chargeduration/60 else 0 END) AS OnNet_Minutes, sum(CASE WHEN LEFT(cparty,3) in ('996','995') THEN chargeduration/60 else 0 END) AS OffNet_C_Minutes, sum(CASE WHEN LEFT(cparty,3) in ('994','993','992') THEN chargeduration/60 else 0 END) AS OffNet_A_Minutes, sum(CASE WHEN LEFT(cparty,3) in ('991','990') THEN chargeduration/60 else 0 END) AS OffNet_S_Minutes, sum(CASE WHEN LEFT(cparty,2) = '00' THEN chargeduration/60 else 0 END) AS OffNet_T_Minutes, sum(CASE WHEN len(cparty) < 6 and LEFT(cparty,1) <> 0 THEN chargeduration/60 else 0 END) AS SC_Minutes from August.dbo.cdr20130818 where CHARGEDURATION > 0 and ISNULL(region,'''')<>'''' and LEN(region) > 5 group by right(RTRIM(region),5) order by right(RTRIM(region),5) asc 

Answer 1

In your case, you cannot put them all in one CASE , since all the results go into different selection columns.

By the way, you should remove the ISNULL(region, '''') <> '''' condition, since it is redundant if it is associated with the LEN(region) > 5 condition. (If region is null, then LEN(region) also null, and NULL > 5 is false.)

Answer 2

I think everything is fine with you, six different SUM() , each of which makes sense itself.

If all your criteria were in the same CASE expression, you would lose details, you would return the SUM() your individual individual statements combined into one.

Combining redundant criteria in a WHERE can clear the CASE statement, but there is nothing superfluous here.