After following every thread I could find on this and not getting it to work I would love to get some input on what I might be doing wrong. I am aggregating a number of int2 fields using Coalesce. I only get values when every field has values; if there is a null value anywhere (and that's common), the result of the whole aggregation is null. I know the nullif() function can replace nulls with a usable value like 0, but whatever I do I either get an error, or 0 as the result of the entire aggregation. Any help appreciated!
Here's the code:
SELECT
"public".jam_fodder_productivity.cows,
"public".jam_fodder_productivity.sheep,
"public".jam_fodder_productivity.lamb,
"public".jam_fodder_productivity.horse,
"public".jam_fodder_productivity.ungneyti,
COALESCE(jam_fodder_productivity.cows + (jam_fodder_productivity.ungneyti / 2::double precision) + (jam_fodder_productivity.horse / 2::double precision) + (jam_fodder_productivity.sheep / 6::double precision) + (jam_fodder_productivity.lamb / 12::double precision)) AS total_productivity
FROM
"public".jam_fodder_productivity
The result of the query looks like this:
You'll have to apply
COALESCE(<expression>, 0)
to each of the expressions that make up the sum.
Otherwise, if any of the expressions is NULL, the whole sum becomes NULL.
So the entry would be calculated as
COALESCE(jam_fodder_productivity.cows, 0)
+ COALESCE(jam_fodder_productivity.ungneyti / 2::double precision, 0)
+ COALESCE(jam_fodder_productivity.horse / 2::double precision, 0)
+ COALESCE(jam_fodder_productivity.sheep / 6::double precision, 0)
+ COALESCE(jam_fodder_productivity.lamb / 12::double precision, 0)
AS total_productivity