Count non empty columns within a single row MySQL

Question

I'm trying to find a pure MySQL way of doing this to avoid a lot of repeat / similar PHP scripts.

Say I select a single row in my table it could look like this:

| col1 | col2 | col3 | col4 | col5 | col6 |
| abc  |b b a | '%kj |  jh  |      |      |

I want to COUNT how many columns in that row are not empty. So in this example it is 4.

I came up with a solution where I run a count query where col1 is not empty for id=X, and then run this 6 times and add up the results. However I feel this is surely very inefficient and I couldn't think of a better way of doing it.

Does anybody have any idea how I could make that more efficient?

ild flue · Accepted Answer · 2018-01-26 13:12:49Z

2

Try this

SELECT 
COUNT(NULLIF(col1,'')),
COUNT(NULLIF(col2,'')),
COUNT(NULLIF(col3,'')),
COUNT(NULLIF(col4,'')),
COUNT(NULLIF(col5,'')),
COUNT(NULLIF(col6,''))
FROM yourTableName

I tried it in my local phpMyAdmin, works well. :)

edited Jan 26, 2018 at 13:12

answered Jan 26, 2018 at 13:09

ild flue

1,3819 silver badges9 bronze badges

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

5 Comments

James Over a year ago

Perfect! Thank you, I'll accept the answer when I can in 5 minutes :)

James Over a year ago

Oh just a small one you need to add a comma after the COUNT col5 line to make the syntax correct for anyone that comes here to copy paste

dhinchliff Over a year ago

This does not do what you asked for, this counts vertically and you asked to count not null values in the same row.

James Over a year ago

It worked fine just added a WHERE id=x condition at the end which works well for me

dhinchliff Over a year ago

Also the NULLIF is redundant, COUNT does not count null values anyway. And this does not return 4. It returns 1,1,1,1,0,0.

dhinchliff · Accepted Answer · 2018-01-26 13:27:20Z

0

To count not null values in each row use:

SELECT 
CASE WHEN col1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN col2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN col3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN col4 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN col5 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN col6 IS NOT NULL THEN 1 ELSE 0 END 
FROM my_table;

answered Jan 26, 2018 at 13:27

dhinchliff

1,2039 silver badges17 bronze badges

Collectives™ on Stack Overflow

Count non empty columns within a single row MySQL

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related