CODEWITHSUNDEEP
sqlselect
ambigfamous
ambigfamous

Reputation: 23

how to get the count of columns with same value for each row in sql

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

Upvotes: 1

Views: 46

Answers (2)

MBadrian
MBadrian

Reputation: 409

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

Upvotes: 0

Mureinik
Mureinik

Reputation: 311808

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

Upvotes: 2

Related Questions