mysql - Count the number of columns which are not null -

room:

+---------+---------+-----------+-------------+-------------+-------------+---------+ | room_id | room_no | room_stat | room_name   | player_a_id | player_b_id | turn_of | +---------+---------+-----------+-------------+-------------+-------------+---------+ |       1 |       1 |         0 | blah        |           0 |           0 |       0 | |       2 |       5 |         0 | second room |           1 |           3 |       0 | |       3 |       3 |         0 | 3rd room    |           0 |           0 |       0 | |       4 |       4 |         0 | 4th room    |           0 |           0 |       0 | +---------+---------+-----------+-------------+-------------+-------------+---------+ 

i want count how many players inside room example:

• if player_a_id !=0 or !null , player_b_id !=0 or !null count 2
  
• if player_a_id!=0 or player_b_id!=0 count 1
  
• else if both player_a_id , player_b_id null return 0;

you might try like:

select room_id,        ((case when player_a_id <> 0 , player_a_id not null 1 else 0 end) +         (case when player_b_id <> 0 , player_b_id not null 1 else 0 end)        ) numinroom room; 

the comparisons null unnecessary. following have same effect:

select room_id,        ((case when player_a_id <> 0 1 else 0 end) +         (case when player_b_id <> 0 1 else 0 end)        ) numinroom room;