How to count non empty cols ? mysql

Question

I have this table :

id  col1  col2  col3
 1   ''    ''    bla
 2   ''    bla   bla 
 3   bla    ''    ''

I want to build a query that will retun how much cols are not empty for each id.

so the result would be :

How to do that ?

jkavalik · Accepted Answer · 2015-06-11 15:39:45Z

2

select id, ((col1 != '') + (col2 != '') + (col3 != '')) as non_empty_count
from table;

Uses a trick where boolean true=1 and false=0 - so for each column non empty value ads 1 to count. Can be seen in action at http://sqlfiddle.com/#!9/751df2/5

answered Jun 11, 2015 at 15:39

jkavalik

1,31611 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

54l3d Over a year ago

I like this answer, I didnt know that boolean in Mysql is 0/1

jkavalik Over a year ago

@54l3d yes, nice for "sum(is_active = 1) as active_count" too

Collectives™ on Stack Overflow

How to count non empty cols ? mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related