How to SUM rows having same uid in two tables individually in SQL?

August 25, 2022

I am using PostgreSQL.
I have two tables. Each having there own metric. I want to sum the metrics having same uid in each table induvidually and show them. Consider

I have table A

uid   metric_1  timestamp
a1.    10.      x
a2.    5.       x
a1.    10.      x
a1.    2.       x

Table 2 has

uid   metric_2  timestamp
a1.    1          x
a2.    3          x
a1.    5          x
a1.    5          x

I want my result to be when i query for a1 for given timestamps

uid   metric_1_sum  metric_2_sum
a1.      22              11

I tried join like this

SELECT a.uid, SUM(a.metric_1) as metric_1_sum, SUM(b.metric_2) as metric_2_sum from table_a as a LEFT JOIN table_b as b ON a.uid = b.uid WHERE a.timestamp >= 1622620531000 and a.timestamp <= 1625212531000 AND a.uid = 'a1'  GROUP BY a.uid HAVING SUM(a.metric_1)>1;

However, it returns me incorrect sum. I dont know why.

uid.  metric_1_sum.  metric_2_sum
a1.      66.            33

Any helps are highly appreciated.
I have given the sql template for u here

CREATE TABLE table_a (uid, metric_1, timestamp);
INSERT INTO table_a (uid, metric_1, timestamp)
  VALUES  ("a1", "10", "x"), ("a2", "5", "x"), ("a1", "10", "x"), ("a1", "2", "x");

CREATE TABLE table_b (uid, metric_2, timestamp);
INSERT INTO table_b (uid, metric_2, timestamp)
  VALUES  ("a1", "1", "x"), ("a2", "3", "x"), ("a1", "5", "x"), ("a1", "5", "x");

>Solution :

You don’t need to join them, you should union [all] them and then apply the sum.

   select t.uid, SUM(t.metric_1) as metric_1_sum, SUM(t.metric_2) as metric_2_sum
    from(
     select a.uid, a.metric_1, 0 as metric_2 
     from  table_a
     WHERE a.timestamp >= 1622620531000 
       and a.timestamp <= 1625212531000 AND a.uid = 'a1'
    union all
    select b.uid, 0 as metric_1, b.metric_2 
    from table_b as b
    WHERE b.timestamp >= 1622620531000 
     and b.timestamp <= 1625212531000 AND b.uid = 'a1'
    )t
GROUP BY t.uid HAVING SUM(t.metric_1)>1