RE: Joining 2 Tables

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Sun, 30 Dec 2001 16:46:04 -0800
Message-ID: <F001.003E4557.20011230162516@fatcity.com>

select act.ccid, act.total, nvl(bud.total,0) from
(select ccid,sum(act) total from actual group by ccid) act, (select ccid,sum(bud) total from budget group by ccid) bud where act.ccid = bud.ccid (+)

Regards,

Waleed

-----Original Message-----
To: Multiple recipients of list ORACLE-L Sent: 12/30/01 4:30 PM

Hi all,

I have been trying to get this right but to no avail.

I have the following tables

Table 1: Actual

Pid, ccid, act
1, 11011102, 798.88
2, 11011102, 666.79

Table 2: Budget
Pid, ccid, act
1, 11011102, 672.02
2, 11011102, 40.30

I need to write an SQL to get the following result in one row for each ccid
value that may appear in any of/both the tables:

ccid, sum(act), sum(bud)
11011102, 1465.67, 712.32

But the best I can get is:

  1 select a.ccid, sum(a.act)
  2 from actual a
  3 where
  4 a.ccid = 1011102
  5 group by a.ccid
  6 union
  7 select b.ccid, sum(b.bud)
  8 from budget b
  9 where
10 b.ccid = 1011102
11* group by b.ccid
SQL> /       CCID SUM(A.ACT)
---------- ----------

1011102 712.32
1011102 1465.67

Any idea how the sql should be written?

Would greatly appreciate any advice.

Cheers!

Pete

Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Pete Wang
INET: peisiong_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Khedr, Waleed
INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------