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
  • Please try not to use images when possible. Instead add the CREATE TABLE ... and INSERT INTO.... statements to your question to allow for easy reproduction of the issue/question. I've edited your question to show you how this can be achieved. It allows for better understanding and can provide a way to produce a working environment for e.g. db<>fiddle as I added in Akina's answer. Welcome to DBA.SE.
    – John K. N.
    Jan 27, 2020 at 12:37
  • @Johnakahot2use - thanks for the edit and guidance.
    – AWGIS
    Jan 27, 2020 at 14:02

1 Answer 1

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.

  • Thank you - so if I wanted to sum a "Measure_2" would I put SUM(t2.measure_1), SUM(t2.measure_2)?
    – AWGIS
    Jan 27, 2020 at 11:43
  • @AWGIS I think you may simply test and look at the result correctness... :)
    – Akina
    Jan 27, 2020 at 11:45
  • 1
    I slightly edited your answer to provide a working example on db<>fiddle and a hint about views. I hope you don't object, as I was trying to create a working example of a good, high quality and lasting Q & A. Please feel free to rollback your answer if you feel I have abused your answer to a degree that does not meet your initial intention.
    – John K. N.
    Jan 27, 2020 at 12:42
  • 1
    @Johnakahot2use I very rarely object to editing and adding to my answers. I sometimes find it difficult to give explanations - English is not my native language. Thank you.
    – Akina
    Jan 27, 2020 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.