Using Case?

[eggy168]

Hi, I have a table,

tbl_A
Column1,Column2,Column3
AA 100 0
BB 100 0
CC 0 0
DD 0 100
EE 100 0
EE 0 500
In Access, I can use IIF to figure this out by using this syntax,

Column_Sum: SUM(IIF(tbl_A.Column2 <> 0 ,1, IIF(tbl_A.Column3<>0, 1, 0)))

Result:
Column1 Column_Sum
AA 1
BB 1
CC 0
DD 1
EE 2

I would like to know if I am using SQL Script in SQL Server, how can I have the same result? I tried this, but it gives me an error message:

SELECT
Column1,
SUM(Case WHEN Column2 <> 0 , Then '1')
ELSE
(Case WHEN Column3 <>0, THEN '1')
END As Column_SUM
FROM tbl_A

Can someone help?
Thank You.

 

[bborissov]

SELECT Column1,
        SUM(CASE WHEN Column2 <> 0 OR
                      Column3 <> 0
                      THEN 1
             ELSE 0 END) As Column_SUM
FROM tbl_A

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[gmmastros]

With Access, you needed to write a nested IIF query to accomplish this. The Case/When syntax is more powerful so that you don't need to nest it. You can, but you don't need to. Nesting the Case/Whens makes the code more difficult to read.

The following code shows your sample data in a table variable, and then it shows 2 queries to return the data you're looking for.

Declare @tbl_A Table(Column1 VarChar(20), Column2 int, Column3 Int)
Insert Into @tbl_A Values('AA',      100       , 0)
Insert Into @tbl_A Values('BB',      100       , 0)
Insert Into @tbl_A Values('CC',      0         , 0)
Insert Into @tbl_A Values('DD',      0         , 100)
Insert Into @tbl_A Values('EE',      100       , 0 )
Insert Into @tbl_A Values('EE',      0         , 500)

Select Column1,
       Sum(Case When Column2 <> 0 Then 1
                When Column3 <> 0 Then 1
                Else 0
                End) As Column_Sum
From   @tbl_A
Group By Column1

SELECT 
Column1,
SUM(Case WHEN Column2 <> 0 Then 1
ELSE
Case WHEN Column3 <>0 THEN 1 Else 0 End
END )As Column_SUM
FROM @tbl_A
Group By Column1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[eggy168]

Hi,
I wonder what's the different if I have the "Count" word?

SELECT
Column 1
COUNT(CASE WHEN Column2 <> 0 THEN 1
WHEN
Column3 <> 0 THEN 1 END)
AS
Column_Sum
FROM
tbl_A
GROUP BY Column1

Also, Gmmastros, why I need two "End" at the end of before the "As" word?

Again, thanks for all your help.

 

[gmmastros]

I wonder what's the different if I have the "Count" word?

There wouldn't be any difference in the results. Counting and Adding 1 (for each) is the same thing. There may, as some very low level, be a difference in performance, but this is most certainly negligible.

Also, Gmmastros, why I need two "End" at the end of before the "As" word?

The 2 ends only exist in the nested case statement, which you really don't need. There are several rules you should follow when you use a case statement. Some of the rules are necessary, and some are simply best practices.

To use Case/When (mandatory):
1. Every Case statement must have at least one WHEN statement.
2. Every Case statement must have exactly one END statement.

To use Case/When (strongly suggested):
1. Every branch of execution should return the same data type.

When writing a case/when, I like to format the code so that the WHEN, THEN, ELSE & END are at the same indent level. I think it makes the code easier to read. Now, let's take a look at the 2nd query with the nested case statements.

SELECT Column1,
       SUM(Case WHEN Column2 <> 0 
                THEN 1
                ELSE Case WHEN Column3 <>0 
                          THEN 1 
                          ELSE 0 
                          END
                END )As Column_SUM
FROM   @tbl_A
Group By Column1

Does this makes sense now? If not, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[eggy168]

Yes, it makes more sense now.
Thank you very much, Gmmastros