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

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");

CodePudding user response：

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

CodePudding user response：

Also you can use a subquery like this

SELECT
    a.uid,
    SUM(a.metric_1) metric_1_sum,
    (SELECT 
        SUM(metric_2) 
    FROM table_b b 
    WHERE b.uid = a.uid
          AND b.timestamp BETWEEN 1622620531000 AND 1625212531000) metric_2_sum
FROM table_a a
WHERE a.timestamp BETWEEN 1622620531000 AND 1625212531000 
      AND a.uid = 'a1'
GROUP BY a.uid
HAVING SUM(a.metric_1) > 1