SQLTeam.com | Weblogs | Forums

Help with computed column


#1

Trying to create a computed column formula that will count how many rows are equal to 3 or more. I thought I could get this using a case statement but can't seem to get the logic to work right.

Sample data...


#2

I take it you mean how many "columns" not how many rows.

count_over_2 AS CAST( 
    CASE WHEN one > 2 THEN 1 ELSE 0 END +
    CASE WHEN two > 2 THEN 1 ELSE 0 END +
    CASE WHEN three > 2 THEN 1 ELSE 0 END +
    CASE WHEN four > 2 THEN 1 ELSE 0 END +
    CASE WHEN five > 2 THEN 1 ELSE 0 END
    AS tinyint)

#3

ugh ...must be Monday. Yes, your right: columns...

Thanks for the help. Such a simple solution but couldn't quite grasp it.


#4

That didn't work ... "Error validating the formula for column 'count_over_2'."
Tried playing with it a bit but couldn't get it right.


#5

When trying to save the table it also says "Incorrect syntax near keyword 'AS'


#6

ahhh!!! ...disregard...I got it. Removed the "count_over_2 AS" part and that works. Thanks!


#7

Sorry, the full syntax would be:

ALTER TABLE table_name
ADD count_over_2 AS CAST(...);