SUM() confusion

[lurtnowski]

I have

SELECT SUM(group1_recepticles + group2_recepticles + group3_recepticles) AS sum FROM power_strips WHERE power_strip_id = 2

Why is the result null
heres the table

shouldn’t the result be 8 + NULL + NULL = 8?

[rpkamp]

No. NULL means unknown, so you can’t do calculations with it.

If I have eight apples plus an unknown amount of apples the end result is unknown.

[lurtnowski]

Oh, should I just fo

if(isnull($row['group2_recepticles'])) {$group2 = 0; } else { $group2 = $row['group2_recepticles']; }

[SamA74]

In the context of “group receptacles” does the null really mean 0?
If null means “unknown” or “no data”, but what you are actually saying is “there are no receptacles”, as opposed to “we don’t know how many receptacles there are”, then perhaps it should say 0.

[r937]

@lurtnowski

Oh, should I just fo

no, don’t do that

allow me to introduce you to the COALESCE function

SELECT SUM( COALESCE(group1_recepticles,0)
          + COALESCE(group2_recepticles,0) 
          + COALESCE(group3_recepticles,0) ) AS sum 
  FROM power_strips 
 WHERE power_strip_id = 2

oh, and by the way, you would use SUM() only if there are multiple rows with power_strip_id = 2

if there is only one row, then of course you wouldn’t need SUM() – i mean, it’ll work, but it’s not necessary