skip to Main Content

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

2

Answers


  1. 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
    
    Login or Signup to reply.
  2. 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
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search