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)