I run PostgreSQL and I have multiple (10+) tables with identical column names and formats and I would like to sum two of their columns. If a row is missing in one of the tables, I need it to be considered as 0 (zero).
Two-table EXAMPLE:
TableA:
primkey | year | month | day | data_value
--------+------+-------+-----+-----------
20120101| 2012 | 01 | 01 | 3.7
20120102| 2012 | 01 | 02 | 1.0
TableB:
primkey | year | month | day | data_value
--------+------+-------+-----+-----------
20120101| 2012 | 01 | 01 | 1.3
20120103| 2012 | 01 | 03 | 5.5
Wanted output:
primkey | year | month | day | data_value
--------+------+-------+-----+-----------
20120101| 2012 | 01 | 01 | 5.0
20120102| 2012 | 01 | 02 | 1.0
20120103| 2012 | 01 | 03 | 5.5
I was thinking of doing this:
SELECT a.primkey, a.year, a.month, a.day, a.data_value+b.data_valueas data_value FROM "TableA" as a FULL JOIN "TableB" as b ON a.primkey = b.primkey;
But it produces this (which is missing some of the data_values):
primkey | year | month | day | data_value
--------+------+-------+-----+-----------
20120101| 2012 | 01 | 01 | 5.0
20120102| 2012 | 01 | 02 |
20120103| 2012 | 01 | 03 |
Note: primkey is unique and a PRIMARY KEY and this needs to be valid for joining 10+ tables