Joining 2 Tables

From: Pete Wang <peisiong_at_hotmail.com>
Date: Sun, 30 Dec 2001 16:07:12 -0800
Message-ID: <F001.003E453E.20011230153018@fatcity.com>

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). Received on Sun Dec 30 2001 - 18:07:12 CST