Case statement that adds column values
13,854
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
Author by
user973671
Updated on June 17, 2022Comments
-
user973671 almost 2 years
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
-
user973671 about 12 yearsYes I tried IsNull(race1, 0), on all and that removes the null values and replaces them with 0, but I still cannot add the rest. Thanks
-
Krishnanunni Jeevan about 12 yearsYou can use cast for conversion.I think the example is already given above