CODEWITHSUNDEEP
sqlpostgresql
xxx_coder_noscope
xxx_coder_noscope

Reputation: 85

Postgresql how to sum across two different tables?

I have a two tables:

|currency_type|user_id|amount|
|-------------|-------|------|
|          aaa|    404|  2000|
|          bbb|    404|   300|
|          ccc|     22|   444|

|currency_type|user_id|balance|
|-------------|-------|-------|
|          aaa|    404|     30|
|          xxx|    404|     50|

How can i get sum of both tables (amount + balance) for each currency_type respectively? Example of result for user_id = 404 And performance is matter for me, so faster is better.

|currency_type|user_id|    sum|
|-------------|-------|-------|
|          aaa|    404|   2030|
|          bbb|    404|    300|
|          xxx|    404|     50|

Upvotes: 0

Views: 595

Answers (1)

zedfoxus
zedfoxus

Reputation: 37099

Try this:

create table t1 (currency_type text,user_id int,amount int);
create table t2 (currency_type text,user_id int,balance int);

insert into t1 values
('aaa',    404,  2000),
('bbb',    404,   300),
('ccc',     22,   444);

insert into t2 values
('aaa', 404, 30),
('xxx', 404, 50);

with t1t2 as (
  select currency_type, user_id, amount from t1
  union all
  select currency_type, user_id, balance as amount from t2
)
select currency_type, user_id, sum(amount)
from t1t2
where user_id = 404
group by currency_type, user_id

Result

currency_type | user_id |  sum
:------------ | ------: | ---:
aaa           |     404 | 2030
bbb           |     404 |  300
xxx           |     404 |   50

Example

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=89c7d5b9ee8d5ce1eb42ee5cda961d0c

Explanation

Just combine both tables but rename balance as amount for just the time being in the query. Now you'd have balances and amounts in the same table. All that remains is total up the amount column for each currency_type/user_id combination.

Upvotes: 2

Related Questions