CODEWITHSUNDEEP
sqlsql-servercase
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Case for range of numbers excluding specific number - SQL Server

I have this query

SELECT * ,
CASE WHEN FigureID >= 0 AND <= 10 AND <> 5
THEN 'Group1' ELSE 'Group2' END AS 'Group'
FROM
Figure

Now i am looking to include all numbers between 1-10 in the case statement but exclude 5. Is there anyway to do so that '5' is excluded specifically.

I have tried this but it still doesn't give me the result i am looking for.

 SELECT * ,
 CASE WHEN FigureID <= 4 AND FigureID >= 6
 THEN 'Group1' ELSE 'Group2' END AS 'Group'
 FROM
 Figure

Example result

FigureID    Amount  Group
   1          10    Group1
   2          10    Group1
   3          10    Group1
   4          10    Group1
   5          10    N/a
   6          10    Group1

SQL fiddle - http://sqlfiddle.com/#!6/30e2c/11

thanks

Upvotes: 1

Views: 953

Answers (4)

NP3
NP3

Reputation: 662

    SELECT * ,
    CASE WHEN (FigureID >= 1 AND FigureID <= 10) AND (FigureID <> 5)
    THEN 'Group1'
    WHEN FigureID = 5 THEN 'N/A' 
    ELSE 'Group2'
    END AS [Group]
    FROM Figure

First CASE takes care of the condition that FigureID is between 1 and 10 but not equal to 5 and the output is 'Group1'.

Second CASE tackles the situation where FigureID has a value of 5 and outputs 'N/A'

ELSE clause is if above two conditions are false and outputs 'Group2'.

Finally, this column's name is Group but since Group is a keyword we must enclose it within square braces like [Group].

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270421

One method is:

(CASE WHEN FigureID >= 1 AND FigureID <= 10 AND FigureId <> 5
      THEN 'Group1'
      ELSE 'Group2'
 END) AS grp

You can also do:

(CASE WHEN FigureId = 5 THEN 'N/A'
      WHEN FigureID >= 1 AND FigureID <= 10 THEN 'Group1'
      ELSE 'Group2'
 END) AS grp

Also important: Don't use single quotes for column aliases. Only use single quotes for string and date constants. Also, avoid using SQL keywords as identifier names (GROUP is a SQL keyword).

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

You should use the column name for each condition 

    SELECT * ,
        CASE WHEN FigureID >= 0 AND FigureID <= 10 AND FigureID <> 5
             THEN 'Group1' 
             ELSE 'Group2' 
        END AS 'Group'
    FROM  Figure

Upvotes: 2

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

Just use not equal to 5 instead

SELECT * ,
 CASE WHEN FigureID <> 5
 THEN 'Group1' 
when FigureID  =5
then 'n/a'
ELSE 'Group2' END AS 'Group'
 FROM
 Figure

Upvotes: 0

Related Questions