Search code examples
sqlleft-joinsnowflake-cloud-data-platformcalculated-columns

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

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?

Solution

  • 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