SQL & PL/SQL

What is the realtionship between table A and B, Also in the expected output it is mentioned as A.id, whereas the table A has the column name as COL A.

Thank You

sorry my mistake...a.col1 instead of id and the relation is col1.

i coluld think of 1 query:

SELECT   a.col1, SUM (col2), MIN (b.rec)
    FROM Tablea a,
         (SELECT   col1, SUM (col2) rec
              FROM tableb
          GROUP BY col1) b
   WHERE a.col1 = b.col1
GROUP BY a.col1

but is there any other way?

ooopss, sory..

WITH A AS (SELECT 1 COL1, 10 COL2 FROM DUAL UNION ALL
           SELECT 1 COL1, 10 COL2 FROM DUAL UNION ALL
           SELECT 1 COL1, 10 COL2 FROM DUAL UNION ALL
           SELECT 2 COL1, 40 COL2 FROM DUAL 
           )
    ,B AS (SELECT 1 COL1, 10 COL2 FROM DUAL UNION ALL
           SELECT 1 COL1, 10 COL2 FROM DUAL UNION ALL
           SELECT 2 COL1, 50 COL2 FROM DUAL UNION ALL
           SELECT 2 COL1, 50 COL2 FROM DUAL
          ) --Creating smaple data

SELECT DISTINCT A.COL1,(SELECT SUM(A.COL2)FROM A WHERE A.COL1=B.COL1)SUM_A,
              (SELECT SUM(B.COL2)FROM B WHERE A.COL1=B.COL1) SUM_B
FROM  A,B
WHERE A.COL1=B.COL1

Here is a try with full outer join:

SQL> WITH A AS (SELECT 1 col1, 10 col2 FROM DUAL UNION ALL
  2             SELECT 1 col1, 10 col2 FROM DUAL UNION ALL
  3             SELECT 1 col1, 10 col2 FROM DUAL UNION ALL
  4             SELECT 2 col1, 40 col2 FROM DUAL UNION ALL
  5             SELECT 4 col1, 140 col2 FROM DUAL),
  6       B AS (SELECT 1 col1, 10 col2 FROM DUAL UNION ALL
  7             SELECT 1 col1, 10 col2 FROM DUAL UNION ALL
  8             SELECT 2 col1, 50 col2 FROM DUAL UNION ALL
  9             SELECT 2 col1, 50 col2 FROM DUAL UNION ALL
 10             SELECT 3 col1, 150 col2 FROM DUAL)
 11  -- End of sample data
 12  SELECT   NVL(s_a.col1, s_b.col1) AS col1, sum_a, sum_b
 13      FROM (SELECT   col1, SUM(col2) AS sum_a
 14                FROM a
 15            GROUP BY col1) s_a
 16           FULL OUTER JOIN
 17           (SELECT   col1, SUM(col2) AS sum_b
 18                FROM b
 19            GROUP BY col1) s_b ON s_a.col1 = s_b.col1
 20  ORDER BY 1;

      COL1      SUM_A      SUM_B
---------- ---------- ----------
         1         30         20
         2         40        100
         3                   150
         4        140

Urs

CHECK OUT THIS QUERY

SELECT A.COL1, SUM(A.COL2) , SUM(B.COL2)
FROM A,B
WHERE A.COL1 = B.COL1
GROUP BY A.COL1;

I like emulating Outer Union :-)
I imaged Venn diagram http://en.wikipedia.org/wiki/Venn_diagram B-)

WITH A AS (SELECT 1 col1, 10 col2 FROM DUAL UNION ALL
           SELECT 1 col1, 10 col2 FROM DUAL UNION ALL
           SELECT 1 col1, 10 col2 FROM DUAL UNION ALL
           SELECT 2 col1, 40 col2 FROM DUAL UNION ALL
           SELECT 4 col1, 140 col2 FROM DUAL),
     B AS (SELECT 1 col1, 10 col2 FROM DUAL UNION ALL
           SELECT 1 col1, 10 col2 FROM DUAL UNION ALL
           SELECT 2 col1, 50 col2 FROM DUAL UNION ALL
           SELECT 2 col1, 50 col2 FROM DUAL UNION ALL
           SELECT 3 col1, 150 col2 FROM DUAL)
select col1,sum(aVal),sum(bval)
from (select col1,col2 as aVal,to_number(null) as bVal from a
      union all
      select col1,to_number(null),col2 from b)
group by col1
order by col1;

COL1  SUM(AVAL)  SUM(BVAL)
----  ---------  ---------
   1         30         20
   2         40        100
   3       null        150
   4        140        null

will gve u wrong results!!!!test it!!