Merge tables with PostgreSQL

Question

The title of this question is not accurate but I didn't know how to summarize it. Please feel free to write it again if you can!

Here is an extract of two tables:

Table table_a

code  | year   | nb_a
------+--------+------
  A1  |   2017 |    1      
  A2  |   2012 |    2
  A3  |   2014 |    2

Table table_b

code  | year   | nb_b
------+--------+------
  A1  |   2013 |    1
  A1  |   2014 |    1
  A2  |   2012 |    1

I need to merge these tables in order to get this output:

code  | year   | nb_a | nb_b | total
------+--------+------+------+-------
  A1  |   2013 |    0 |    1 |     1
  A1  |   2014 |    0 |    1 |     1
  A1  |   2017 |    1 |    0 |     1
  A2  |   2012 |    2 |    1 |     3
  A3  |   2014 |    2 |    0 |     2

I can't find the correct query. I would need something like below (I know it doesn't do the job) but how to get all codes and years merged in one table as codes and years are not all repeated in both tables...

SELECT 
  code,
  "year",
  table_a.nb_a,
  table_b.nb_b,
  table_a.nb_a + table_b.nb_b AS total

FROM table_a, table_b
WHERE table_a.code = table_b.code;

Here are the SQL scripts to create the above tables rapidly:

CREATE TABLE public.table_a (code TEXT, "year" INTEGER, nb_a INTEGER);
CREATE TABLE public.table_b (code TEXT, "year" INTEGER, nb_b INTEGER);

INSERT INTO public.table_a (code, "year", nb_a) VALUES (A1, 2017, 1), (A2, 2012, 2), (A3, 2014, 2);
INSERT INTO public.table_b (code, "year", nb_b) VALUES (A1, 2013, 1), (A1, 2014, 1), (A2, 2012, 1);

Not sure to understand your question... Codes are geographic areas codes, not IDs. — wiltomap
– wiltomap, Commented Jun 15, 2017 at 8:45

Vao Tsun · Accepted Answer · 2017-06-15 08:59:08Z

3

yu probably are looking for FULL OUTER JOIN

SELECT
  coalesce(a.code,b.code),
  coalesce(a."year",b.year),
  coalesce(a.nb_a,0),
  coalesce(b.nb_b,0),
  coalesce(a.nb_a,0) + coalesce(b.nb_b,0) AS total
FROM table_a a full outer join table_b b on a.code = b.code and a.year = b.year;
 coalesce | coalesce | coalesce | coalesce | total
----------+----------+----------+----------+-------
        1 |     2013 |        0 |        1 |     1
        1 |     2014 |        0 |        1 |     1
        1 |     2017 |        1 |        0 |     1
        2 |     2012 |        2 |        1 |     3
        3 |     2014 |        2 |        0 |     2
(5 rows)

edited Jun 15, 2017 at 8:59

answered Jun 15, 2017 at 8:50

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

wiltomap Over a year ago

Thanks so much, it's nearly that! I need the total column to be filled and also zero values instead of NULLs.

wiltomap Over a year ago

What about zero values?

wiltomap Over a year ago

Still have a problem when your solution is applied to a 4 tables join. See new post opened here: stackoverflow.com/q/44564673/2508539. Thanks again for help!

Vao Tsun Over a year ago

@wiltomap answered that one as well - either use parenthesis or strict order or right ON to get wanted result

Collectives™ on Stack Overflow

Merge tables with PostgreSQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related