SUM((((((((((`generalSum`.`total` + `FFBHARVESTINGsum`.`total`) + `PRUNINGsum`.`total`) + `FIELDMAINTENANCEsum`.`total`) + `PESTDISEASEsum`.`total`) + `PLANTINGSUPPLYINGsum`.`total`) + `BUILDINGCONSTRUCTIONsum`.`total`) + `INFRAMAINTENANCEsum`.`total`) + `NURSERYsum`.`total`) + `TRANSPORTATIONsum`.`total`)) AS `totalOfall`
The above seems to return null, i have tried coalesce but that doesn't help either. the tables are all views, could that cause an issue?
You have way too many parentheses in your expression -- but that doesn't affect the results, only the readability and maintainability.
The SUM() returns NULL only when the expression in the SUM() is NULL on all rows. This is because SUM() ignores NULL values, so if even one expression result were not NULL, then the SUM() would be not NULL.
The expression will be NULL if any of the values on a row is NULL. That is because adding NULL to any value returns NULL -- in contrast to SUM(). So, I assume that one or more of the values are NULL in any given row.
You can fix this by replacing NULL values with 0 using COALESCE():
sum( coalesce(generalSum.total, 0) +
coalesce(FFBHARVESTINGsum.total, 0) +
coalesce(PRUNINGsum.total, 0) +
. . .
)
This has nothing to do with using a view per se.
Probably one of the value is NULL. If that, whole sum equals NULL.
Wrap every column in
IFNULL(value, 0)
