A usually simple SQL join

Question

I need advice on doing a JOIN with PostgreSQl. I want to take the sum (or number of times id 1 is entered) of a single id and place it into a new column in table b.

Table a

id  username   comment
1    Bob        Hi
2    Sally      Hello
1    Bob        Bye

Table b

id    something   total_comments
1       null            2

Why? kind what analytical functions can be used for. Do you have a performance reason to store this value? as A changes (new or deleted records) does B's count need to be updated? (even more of a reason to count at run time if it does) — xQbert, May 24 '18 at 21:16
Select into ? `SELECT * INTO B FROM (Select ID, count(*) from A group by ID) A` But is this one time load. Are you needing each time something changes in A to update B?? And lastly, what's this have to do with a a "JOIN"? Similar question to: https://stackoverflow.com/questions/8250389/computed-calculated-columns-in-postgresql but i'm not sure what you're after — xQbert, May 24 '18 at 21:20

score 0 · Answer 1 · answered May 24 '18 at 21:20

0

Create a trigger for insert, update, delete on the Table a to select the sum and update in Table b

answered May 24 '18 at 21:20

Kerols Alfons

59
1
10

score 0 · Answer 2 · answered May 24 '18 at 21:24

You could use SELECT INTO if table_b doesn't already exist.

SELECT
    id
  , NULL AS something
  , COUNT(comment) AS total_comments
  INTO table_B
FROM table_a
GROUP BY id

Or INSERT INTO if table_b does exist.

INSERT INTO table_b (id, something, total_comments)
SELECT
    id
  , NULL AS something
  , COUNT(comment) AS total_comments
FROM table_a
GROUP BY id

A usually simple SQL join

2 Answers2