Adding a calculated column while joining tables in Snowflake

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

​x
 
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;

 
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?

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

Advertisement

Answer