MySQL avoiding null values for sum operations?

Question

I need to make a column that counts the amount of variables which are above 0, and that won't return null if there is any non-null value in it, for each value in an id column.

What I managed to make is using a sum between some boolean operations:

IF 'A' THEN 'B' ELSE 'C' (at least that's what I've got)

select ID, `jul`, `aug`, `set`, `oct`, `nov`, `dec`,
((((not `jul`) or 1) and (`jul` or 0)) 
+(((not `aug`) or 1) and (`aug` or 0))
+(((not `set`) or 1) and (`set` or 0))
+(((not `out`) or 1) and (`out` or 0))
+(((not `nov`) or 1) and (`nov` or 0))
+(((not `dec`) or 1) and (`dec` or 0))) as sum from table;

It works at first view, but if there is any null value in a line, the sum returns null for each respective id.

What could I do to avoid this problem?

"set" should prolly be "sep", btw...

user610217
– user610217

2016-09-09 17:36:40 +00:00
Commented Sep 9, 2016 at 17:36 — user610217
– user610217, Commented Sep 9, 2016 at 17:36

israel · Accepted Answer · 2016-09-09 18:03:58Z

1

try

SUM( IFNULL(jul,0)+IFNULL(ago,2) ) as sum from table

/* 
   obs: the SUM is good to sum multiple values
   IFNULL returns 0 to the sum if jul is null and 2 for ago if ago is null in the example.
*/

i think it works. :)

answered Sep 9, 2016 at 18:03

israel

3701 gold badge2 silver badges9 bronze badges

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

2 Comments

jlsalles Over a year ago

the ifnull did help me with my case. At first, using it did a sum of the variables, but since i wanted de sum their boolean values (sums of 0s and 1s), i combined it with my logical operations and it worked ^^ ifnull((((not jul) or 1) and (jul or 0)),0)

jlsalles Over a year ago

I also wanted for the sum to show null for everytime all of its variables have null values, is there a way for such?

user610217 · Accepted Answer · 2016-09-09 17:39:13Z

1

You need to use a coalesce or variant to deal with nulls. Null value represents an unknown. You can't add unknowns and get something other than an unknown.

NULL + 1 = NULL
COALESCE(NULL, 0) + 1 = 1

answered Sep 9, 2016 at 17:39

user610217

Comments

Nisse Engström · Accepted Answer · 2016-09-10 16:20:11Z

-1

Use isnull(columnname, 0) to turn nulls into 0s. So isnull('jul',0)+isnull('aug',0) etc.

edited Sep 10, 2016 at 16:20

Nisse Engström

4,77323 gold badges29 silver badges44 bronze badges

answered Sep 9, 2016 at 17:41

xenapan

877 bronze badges

Collectives™ on Stack Overflow

MySQL avoiding null values for sum operations?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related