eg: table
id a b c d
1 Y Y Y N
2 N Y Y N
what I need is
id a b c d e
1 Y Y Y N 3
2 N Y Y N 2
-
Tag your question with the database you are using.Gordon Linoff– Gordon Linoff2020-03-16 11:17:55 +00:00Commented Mar 16, 2020 at 11:17
Add a comment
|
2 Answers
SQL functions generally aggregate data across rows, not columns, so I don't think there's a generic way of doing this. You could, however, for this usecase, use a series of case expressions:
SELECT id, a, b, c, d,
CASE a WHEN 'Y' THEN 1 ELSE 0 END +
CASE b WHEN 'Y' THEN 1 ELSE 0 END +
CASE c WHEN 'Y' THEN 1 ELSE 0 END +
CASE d WHEN 'Y' THEN 1 ELSE 0 END AS e
FROM mytable
4 Comments
MBadrian
if you value is only Y it is correct but if count N and Y this is not correct answer
Mureinik
@MBadrian According to the OP'd sample data, only Ys should be counted
ambigfamous
hi Mureinik, thank you I got it but I want to insert the e column to existing table please help
MBadrian
Yes but i think about general solution for this problem
UPDATE mytable SET e=
CASE a WHEN 'Y' THEN 1 ELSE 0 END +
CASE b WHEN 'Y' THEN 1 ELSE 0 END +
CASE c WHEN 'Y' THEN 1 ELSE 0 END +
CASE d WHEN 'Y' THEN 1 ELSE 0 END
FROM
1 Comment
W Kristianto
It may be a great answer, but without an explanation or where the error was, it would be hard for anyone to find it useful.