CODEWITHSUNDEEP
sqlsql-server-2008case
user973671
user973671

Reputation: 1640

Case statement that adds column values

I need to write a case statement that will return

“1 of 3” if someone voted in one of three elections, 
“2 of 3” if someone voted in two of three elections, 
“3 of 3” if someone voted in three of three elections,

The problem is that some of the values are a varchar and some are null and I cannot add them together. This is my idea but I cannot get it to work.

select 
id,
CASE 
     WHEN race1 + race2 + race3 = 0 then '0-3'
     WHEN race1 + race2 + race3 = 1 then '1-3'
     WHEN race1 + race2 + race3 = 2 then '2-3'
     WHEN race1 + race2 + race3 = 3 then '3-3'
     WHEN race1 + race2 + race3 is null then 'Unknown'
 END AS General_Turnout
from test4

http://sqlfiddle.com/#!3/cac66/3

Upvotes: 5

Views: 15031

Answers (3)

Steve Kass
Steve Kass

Reputation: 7184

From what you posted on SQLfiddle, I assume NULL means did not vote and any string means did vote. You can't add the non-numeric strings you have in columns race1, race2, and race3, but this should work:

SELECT 
  id,
  CAST(
    CASE WHEN race1 IS NOT NULL then 1 ELSE 0 END +
    CASE WHEN race2 IS NOT NULL then 1 ELSE 0 END +
    CASE WHEN race3 IS NOT NULL then 1 ELSE 0 END
  AS CHAR) + '-3' AS General_Turnout
FROM test4

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726509

This should work:

select 
id,
    CAST( (CASE WHEN race1 IS NOT NULL THEN 1 ELSE 0 END)
   +(CASE WHEN race2 IS NOT NULL THEN 1 ELSE 0 END)
   +(CASE WHEN race3 IS NOT NULL THEN 1 ELSE 0 END) AS CHAR) + '-3'
AS General_Turnout
from test4

Upvotes: 9

Krishnanunni Jeevan
Krishnanunni Jeevan

Reputation: 1759

use COELESCE(value,0) so that null is taken as zero

Upvotes: 0

Related Questions