Count occurrence of unique value in a row using SQL

Question

I have a table like this:

ID | B1 | B2 | B3 | B4 | B5 |  
------------------------------------  
1  | N  | N  | N  | N  | N  |
2  | N  | Y  | N  | Y  | Y  |
3  | N  | N  | N  | N  | Y  |

I want a query that should return

ID | Count |  
------------
 1 |   5   |
 2 |   2   |
 3 |   4   |

I tried the one which is used in counting rows. i.e SELECT count(B1+B2+B3+B4+B5) as Count from table. @1000111 — Parth, Nov 10 '16 at 10:22

score -1 · Answer 1 · edited May 23 '17 at 10:30

-1

Note that boolean expression in MySQL resolves into 0/1.

a = b returns 1 only if a is equal to b otherwise 0

SELECT 
  ID,
  (
    (B1 = 'N')+
    (B2 = 'N')+
    (B3 = 'N')+
    (B4 = 'N')+
    (B5 = 'N')
  ) AS Count
FROM your_table

SEE DEMO

Suggestion:

Better normalize your data.

See Normalization in MySQL

edited May 23 '17 at 10:30

Community

1
1

answered Nov 10 '16 at 05:07

1000111

13,169
2
28
37

what if i have more number of Bs. What is I have value upto B40? – Parth Nov 10 '16 at 10:30
Normalize your data. This is the best suggestion in this case. – 1000111 Nov 10 '16 at 10:50
How to normalize. Please elaborate. take a case where B goes upto 30. thank you. – Parth Nov 10 '16 at 13:11
Are there only two types ('Y','N')? – 1000111 Nov 14 '16 at 06:18
Yes, only 2 types – Parth Nov 16 '16 at 01:35

Count occurrence of unique value in a row using SQL

1 Answers1