I have 4 tables A, B, C and D with 3 columns each
A- aa, ab, ac B- ba, bb, bc C- ca, cb, cc D- da, db, dc
I need to join the 4 tables and add a new calculated column (aa + ab + ac). My query is
SELECT A.aa, A.ab, A.ac, B.ba, B.bb, B.bc, C.ca, C.cb, C.cc, D.da, D.db, D.dc,(A.aa + A.ab + A.ac) AS total FROM A LEFT JOIN B ON A.aa = B.ba LEFT JOIN C ON A.ab = C.ca LEFT JOIN D ON A.aa = D.da;
The tables join fine, but the calculated column returns NULL for all rows. Where am I going wrong and is there any other way to do it?
Advertisement
Answer
For completeness, moving the answer from @Fred from a comment to an answer:
Seems like some of aa, ab, ac are NULLs so sum is therefore NULL. Apply
ZEROIFNULL()
function to each column before summing, to treat missing values as zero?
https://docs.snowflake.com/en/sql-reference/functions/zeroifnull.html