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
You don’t need to join them, you should union [all] them and then apply the sum.
Also you can use a subquery like this