Postgresql – Create new table with a sum where row from table 2 matches field in table 1 and aggregate on another field

aggregatejoin;postgresql

I am fairly new to SQL so apologies if my terminology is incorrect. I am looking to summarise a number of measurable's from "Table 2" but in the example below I have only included 1 for ease. I would like to insert the results into a a new table (Table 3).

I use pgAdmin 4.

Tables and contents

Table 1

This has two useful fields. Field 1 is named "Code_1" but this is not a unique field. Field 2 is named "Code_2" and this is unique. This table basically highlights all the features in "Code_2" and shows which features they are linked to in "Code_1".

CREATE TABLE Table_1 (Code_1 int, Code_2 int)

INSERT INTO Table_1 (Code_1, Code_2) VALUES  (1,1), (1,2), (1,3), (2,4), (2,5), (2,6)

code_1 | code_2
-----: | -----:
     1 |      1
     1 |      2
     1 |      3
     2 |      4
     2 |      5
     2 |      6

Table 2

The features in "Code_2" also have a number of measurable's associated with them but as explained above there is only one in this example – "Measure_1".

CREATE TABLE Table_2 (Code_2 int, Measure_2 int)

INSERT INTO Table_2 (Code_2, Measure_2) VALUES (1,50), (2,75), (3,100), (4,25), (5,25), (6,50)

code_2 | measure_2
-----: | --------:
     1 |        50
     2 |        75
     3 |       100
     4 |        25
     5 |        25
     6 |        50

Output table

I would like to create a new table (Table 3) with "Code_1" (unique) which is a sum of all the measures ("Measure_1") associated with "Code_2" which is associated with "Code_1).

code_1 | sum_measure_1
-----: | ------------:
     1 |           225
     2 |           100

Best Answer

SELECT t1.code_1, SUM(t2.measure_1)
FROM table1 t1
NATURAL JOIN table2 t2
GROUP BY t1.code_1

A working example can be found at db<>fiddle where I used the PostgreSQL 10 engine. You can build on this example.

Instead of using a static table, you might want to consider using a view instead. This has the advantage that the values will be calculated on-the-fly and aren't static.

Related Question