returning the number of columns in a row that are zero in mysql

Question

I have a table that contains amongst other things a datetime column, and 4 other columns that contain either a zero or a number. I would like to return how many columns in a row contain a zero in mysql.

for example

1_datetime 0 2 5 0

2_datetime 5 6 3 0

I would like for each datetime to know how many columns have zeros in

so i want

1_datetime 2

2_datetime 1

thanks

Surely you tried some SQL yourself that didn't work? Could you post it? — Colin 't Hart, Sep 17 '14 at 15:10

score 2 · Accepted Answer · answered Sep 17 '14 at 14:13

2

The easiest way is to sum negated values:

SELECT datetime, (!a) + (!b) + (!c) + (!d) AS zero_count
  FROM table
. . . .

Operator ! returns 1 if operand is 0, 0 if operand is non-zero, and NOT NULL if operand is NULL.

Parenthsis added because adding + have higher precedence over negation.

answered Sep 17 '14 at 14:13

Kondybas

4,323
15
13

@srh You have to be sure you haven't NULL values cause you'll get NULL instead of count. – Kondybas Sep 17 '14 at 14:19
i just have a zero value or a reading so it works perfectly thank you – srh Sep 17 '14 at 14:21
@Kondybas ... "returns NOT NULL if operand is NULL" ...? Negating null is still null. – Michael - sqlbot Oct 13 '14 at 10:55
I've just paste an official manual: Evaluates to 1 if the operand is 0, to 0 if the operand is nonzero, and NOT NULL returns NULL – Kondybas Oct 13 '14 at 13:26

returning the number of columns in a row that are zero in mysql

1 Answers1