Home » SQL & PL/SQL » SQL & PL/SQL » Comparison
icon2.gif  Comparison [message #206073] Tue, 28 November 2006 09:57 Go to next message
chmlaeeque
Messages: 59
Registered: September 2006
Member
hi
how can i compare to tables having same columns and consist same codes but with different values assign to codes, how can i view only distinct code from the both tables but sum up there values e.g

t1
code amt
01 100
02 200
03 300

t2
code amt
01 500
02 300


in report i want to view

code amt
01 600
02 500
03 300

thanx
Re: Comparison [message #206081 is a reply to message #206073] Tue, 28 November 2006 11:07 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SELECT   code, SUM (amt)
    FROM (SELECT code, amt
            FROM T1
          UNION
          SELECT code, amt
            FROM T2)
GROUP BY code;
Re: Comparison [message #206082 is a reply to message #206073] Tue, 28 November 2006 11:08 Go to previous message
srinivas.k2005
Messages: 334
Registered: August 2006
Senior Member
Hi,

Check this query,

SQL> create table atest (a varchar2(5),b number);

Table created.

SQL> create table btest (a varchar2(5),b number);

Table created.

SQL> insert into atest values('&a','&b');
Enter value for a: 01
Enter value for b: 100
old 1: insert into atest values('&a','&b')
new 1: insert into atest values('01','100')

1 row created.

SQL> /
Enter value for a: 02
Enter value for b: 200
old 1: insert into atest values('&a','&b')
new 1: insert into atest values('02','200')

1 row created.

SQL> /
Enter value for a: 03
Enter value for b: 300
old 1: insert into atest values('&a','&b')
new 1: insert into atest values('03','300')

1 row created.

SQL> insert into btest values('&a','&b');
Enter value for a: 01
Enter value for b: 500
old 1: insert into btest values('&a','&b')
new 1: insert into btest values('01','500')

1 row created.

SQL> /
Enter value for a: 02
Enter value for b: 300
old 1: insert into btest values('&a','&b')
new 1: insert into btest values('02','300')

1 row created.

SQL> commit;

SQL> select a.a,nvl(sum(a.b),0)+nvl(sum(b.b),0) sum from atest a full outer join btest b
on a.a=b.a
group by a.a;

A SUM
----- ----------
01 600
02 500
03 300
Previous Topic: COUNT funtion help
Next Topic: About Cursors
Goto Forum:
  


Current Time: Fri Dec 02 13:48:59 CST 2016

Total time taken to generate the page: 0.19586 seconds