mysql - Sum, Avg, Max, Min, Count of NULL values -
in mysql select 2+null tbl_name
return null
, since mysql cannot interpret null
number.
but why select sum(quantity) tbl_name
not return null
if 1 of values null
? same goes min
, max
, avg
, etc. since mysql doesn't know null be, shouldn't return null specified functions?
this question, , 1 not have answer. treatment of null
in 2 examples different.
the fundamental problem null
means. commonly, used denote missing values. however, in ansi standard, stands unknown value. i'm sure philosophers devote tomes difference between "missing" , "unknown".
in simple expression (boolean or arithmetic or scalar of sort), ansi defines result of "unknown" in cases of operands "unknown". there exceptions: null , false
false , null null
true, these rare.
for aggregation operations, think of sum()
"sum known values", , on. sum()
treats null
values differently +
. but, behavior standard how databases work.
if want null
value aggregation when any of operands null
, need use case
. think easiest way single column is:
(case when count(col) = count(*) sum(col) end)
Comments
Post a Comment