Case statement that adds column values

case, sql, sql-server-2008

Solution

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

Problem

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

Original source