Counting empty or null fields in SQL

Question

I need to count how many empty fields a row contains in an SELECT COUNT(*) statement

My row holds 11 fields and i only need to count in 4 of them. In two of the columns i need to count if they are empty (NOT NULL), and in two i need to count if they hold the value 0

My statement so far:

SELECT COUNT(*) AS subjectcount FROM Tabel WHERE (col1 OR col2) =0 OR (col3 OR col4) = '' AND id=1

Lets say that
col1 = 0
col2 = 1
col3=' '
col4='something'
my sum should then be 2, since two of the fields holds the value im searching for.

Mureinik · Accepted Answer · 2016-05-13 16:21:33Z

1

MySQL has a neat way of treating booleans as ones or zeroes (for true and false values, respectively) when used in a numeric context. So you could do something like this:

SELECT (col1 = 0) + (col2 = 0) + (col3 = '') + (col4 = '')
FROM   tabel

edited May 13, 2016 at 16:21

answered May 13, 2016 at 16:17

Mureinik

316k54 gold badges402 silver badges406 bronze badges

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

2 Comments

ethan Over a year ago

And then GROUP BY id to get this count for each row.

Mureinik Over a year ago

@ethan no group by is needed - just need to remove the sum call to get a result per row (edited in, thanks!)

juergen d · Accepted Answer · 2016-05-13 16:13:09Z

0

WHERE 0 in (col1, col2)
  OR '' in (col3, col4)

answered May 13, 2016 at 16:13

juergen d

205k40 gold badges305 silver badges377 bronze badges

Collectives™ on Stack Overflow

Counting empty or null fields in SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related