I have a requirement where i want to pick the result from following two tables in below format. t1: a b 1 10 1 20 t2: a b 1 50 1 70 I want the sum of b column for both t1 and t2 where a is having value of 1 to be displayed as below. t1.a sum(t1.b) sum(t2.b) 1 30 120 The following query can achieve this but i would like to know if we can do it in more sufficient way. select t1.a, sum(t1.b) , t2.b from t1 , (select t2.a, sum(t2.b) b from t2 group by t2.a ) t2 where t1.a=t2.a group by t1.a , t2.b Thanks for the help

the choice of version of the decision can depend on a set of the reasons. perhaps, the decision would be other acceptable option : Code (SQL): WITH agg_t1 AS (SELECT a ,SUM(b) b FROM t1 GROUP BY a), agg_t2 AS (SELECT a ,SUM(b) b FROM t2 GROUP BY a) SELECT t1.a,t1.b,t2.b FROM agg_t1 t1,agg_t2 t2 WHERE t1.a = t2.a; OR Code (SQL): SELECT t1.a,t1.b,t2.b FROM agg_t1 AS (SELECT a ,SUM(b) b FROM t1 GROUP BY a) t1 , agg_t2 AS (SELECT a ,SUM(b) b FROM t2 GROUP BY a) t2 WHERE t1.a = t2.a; OR only test... )) Code (SQL): SELECT t1.a,SUM(DISTINCT t1.b) t1_b,SUM(DISTINCT t2.b) t2_b FROM t1,t2 WHERE t1.a = t2.a GROUP BY t1.a