none
SUM in SQL IF Statement

    Question

  • I have a scenario where

    CASE WHEN( A>1

    OR SUM ( CASE WHEN  B IN ('X', 'Y')

    THEN 1 ELSE 0 END) <2 ) THEN 'N'

    ELSE 'Y'

    in this case i am not sure whether the value <2 is correct or not.

    Instead, I think it should be <1, As we have THEN 1 for CASE B.

    Please advice.

    • Moved by Tom PhillipsModerator Wednesday, June 22, 2011 2:42 PM TSQL question (From:SQL Server Database Engine)
    Wednesday, June 22, 2011 2:40 PM

Answers

  • Hi, yes it must be (<2) , try please :

    declare @TAB table (A int,B varchar(5))
    insert @TAB 
    select 1,'X' UNION ALL
    select 1,'X' UNION ALL
    select 1,'Y' UNION ALL
    select 2,'X' UNION ALL
    select 3,'Y' UNION ALL
    select 4,'C' UNION ALL
    select 5,'D'
    select A,
    CASE WHEN( A>1 OR SUM(CASE WHEN B IN ('X', 'Y') THEN 1 ELSE 0 END) <2 ) THEN 'N' ELSE 'Y' END
    From @TAB
    Group by A
    --1 Y
    --2 N
    --3 N
    --4 N
    --5 N
    

     

     

    Best regards
    • Marked as answer by SamSSIS Wednesday, June 22, 2011 7:06 PM
    Wednesday, June 22, 2011 5:13 PM

All replies

  • Hello Sam,

    Welcome to the Transact SQL Forum.  Here are a few things to get you oriented.  First, give a look at these two guidelines for posting questions in the Transact SQL Forum:

    MSDN Transact SQL Forum Posting Guidelines:

       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/af20783d-2489-46e4-b6f4-be0ab6d514cb

          Posted by Clifford Dibble

          http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/ce8989b5-dc58-47d8-af65-db5bcd4f7ee2

          Posted by Phil Brammer

    Next, become aware of the code and samples that you post.  One thing that will help you get better answers for your questions is to make your source code more readable.  Therefore, this forum provides a code formatting tool.  You can find the code tool.  The code tool is identified on the tool pallet by an icon that looks like a tiny window with the symbols “</>”.  Use this tool to insert your sample code; this will help the people that read your code.

    If you are getting some kind of error condition, please provide a full description of the error condition; if you are getting an error message from SQL Server itself, please provide the exact error message is generated.

    Wednesday, June 22, 2011 2:56 PM
    Moderator
  • I agree, B can not be both X and Y so the result will always be 'N'. 

     

    create table ab (a int, b char(1))

     

    insert ab values (3,'A'),(1,'Y'),(0,'X')

     

     

    select CASE WHEN A>1 Then 'N' end

    , SUM ( CASE WHEN  B IN ('X', 'Y') THEN 1 ELSE 0 END) 

    , (select SUM ( CASE WHEN  B IN ('X', 'Y') THEN 1 ELSE 0 END) from ab)

    , CASE WHEN( A>1 OR SUM ( CASE WHEN  B IN ('X', 'Y') THEN 1 ELSE 0 END) <2 ) THEN 'N' ELSE 'Y' END

    from ab

    group by a

    Wednesday, June 22, 2011 3:37 PM
  • Hey,

    Thanks for replying ,

    I need the value if B is both X AND Y then what shoud be the THEN condition??

    If the existing case condition which i have posted is correct. then please tell me its correct.

    Thanks

    Wednesday, June 22, 2011 4:49 PM
  • Post your query and any business logic you have.  As I said B can not be both X and Y.  If you need that then your predicate will also need to be added to the subquery

     

    CASE WHEN( A>1

    OR (select SUM ( CASE WHEN  B IN ('X', 'Y') THEN 1 ELSE 0 END) from ab)  <2 ) THEN 'N'

    ELSE 'Y'

    from table ab 

     

     

    Wednesday, June 22, 2011 4:56 PM
  • Hi, yes it must be (<2) , try please :

    declare @TAB table (A int,B varchar(5))
    insert @TAB 
    select 1,'X' UNION ALL
    select 1,'X' UNION ALL
    select 1,'Y' UNION ALL
    select 2,'X' UNION ALL
    select 3,'Y' UNION ALL
    select 4,'C' UNION ALL
    select 5,'D'
    select A,
    CASE WHEN( A>1 OR SUM(CASE WHEN B IN ('X', 'Y') THEN 1 ELSE 0 END) <2 ) THEN 'N' ELSE 'Y' END
    From @TAB
    Group by A
    --1 Y
    --2 N
    --3 N
    --4 N
    --5 N
    

     

     

    Best regards
    • Marked as answer by SamSSIS Wednesday, June 22, 2011 7:06 PM
    Wednesday, June 22, 2011 5:13 PM
  • Thanks badii,

    I got the thing I want.

     

    Wednesday, June 22, 2011 7:07 PM
  • Why must it be <2 since the condition will never fail?  Just asking.  

     

    declare @TAB table (A int,B varchar(5))

    insert @TAB 

    select 1,'X' UNION ALL

    select 1,'X' UNION ALL

    select 1,'Y' UNION ALL

    select 2,'X' UNION ALL

    select 2,'Y' UNION ALL

    select 3,'Y' UNION ALL

    select 4,'C' UNION ALL

    select 5,'D'

    select A,

    CASE WHEN( A>1 OR SUM(CASE WHEN B IN ('X', 'Y') THEN 1 ELSE 0 END) <2 ) THEN 'N' ELSE 'Y' END

    From @TAB

    Group by A

    Wednesday, June 22, 2011 7:22 PM
  • People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

    You ar not thihnkingin sets yet and what to use your old procerual mindset. Case expressions are not quite IF-THEN-ELSE conrol flow. Here uis oine common SQL Idiom for your narrative

    SELECT column_beta
      FROM Foobar
     GROUP BY column_beta
    HAVING MIN (column_beta) = 'Y'
       AND MAX (column_beta) = 'X'
       AND COUNT(column_beta) = 2;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Thursday, June 23, 2011 2:05 AM
  • Oops! Switch MIN and MAX, My bad.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Thursday, June 23, 2011 2:11 AM