Postgresql combining two tables SUM values with same columns

Question

I have couple of table in my Database and all of them has a same column with name User. and now I want to calculate activity for each user on each table, so now I calculate activity on 2 table and you can see with name Result 1 and Result 2.

Result 1:

+--------+-----------------+
| Users  | Number of Insert|
+--------+-----------------+
| Liam   |        37       |
| Jacob  |        16       |
| Ethan  |        6        |
+--------+-----------------+

Result 2:

+--------+-----------------+
| Users  | Number of Insert|
+--------+-----------------+
| Liam   |        7        |
| Jacob  |        10       |
| Ethan  |        9        |
| Elijah |        12       |
| Noah   |        5        |
+--------+-----------------+

so I want to combine this tow table but in user column I want remove delicate
and in Number of Insert column I want SUM same users and if there was no same users then show without sum for example: Elijah , Noah there is no value in Result 1 Table so finally question is here I want output to be like this but how?

OUTPUT :

+--------+-----------------+
| Users  | Number of Insert|
+--------+-----------------+
| Liam   |        44       |
| Jacob  |        26       |
| Ethan  |        15       |
| Elijah |        12       |
| Noah   |        5        |
+--------+-----------------+

NOTICE : in the Result 1 and Result 2, Users column value most of the time are same but not always.

Hambone · Accepted Answer · 2019-01-16 12:42:30Z

3

Full outer join and a coalesce?

 select
   coalesce (r1.users, r2.users) as users,
   coalesce (r1.number_of_insert, 0) + coalesce (r2.number_of_insert, 0)
 from
   result_1 r1
   full outer join join result_2 r2 on
     r1.users = r2.users

This presupposes your result 1 and 2 datasets are already formed as you have above.

answered Jan 16, 2019 at 12:42

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2019-01-16 12:41:44Z

2

If I understand correctly, you can use union all and group by:

select user, sum(num)
from ((select user, num
       from q1
      ) union all
      (select user, num
       from q2
      )
     ) q
group by user;

answered Jan 16, 2019 at 12:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Postgresql combining two tables SUM values with same columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related