2 sums for 2 different fields in 2 different tables for 1 account

[programmer22]

I am trying to do the following. Every drug (or ndc, which is an 11-
digit number that designates a drug) has an amount of money spent on
it in a table called "PlanRebate" (in a field called "amount") and
another amount of money listed in a backup table called
"ClientBackUp" (in a field called "rebate"). There are several
"amounts" for 1 ndc in the "PlanRebate" table, as well as several
"rebates" for 1 ndc in the "ClientBackUp" table. I would like for
each ndc, to have both a sum of all its "amounts" that are found in
the "PlanRebate" table listed in 1 column, and a sum of all of its
"rebates" in the "ClientBackUp" table listed in the second column,
i.e. it would look like this:

ndc sum-of_amounts sum_of_rebates
12345678901
25 36
98765432109
50 41

Basically, I want to do a "group by," but since there will be 2 sums
in the query, I must tell the "group by" that it should group only by
the "amount" field in the "PlanRebate" table for the "sum_of_amounts"
field, and that it should group only by the "rebate" field in the
"ClientBackUp" table for the "sum_of_rebates" field - it should NOT
group by both fields, for both sums. I am pretty sure that this must
be done with a join. How can I do this? I tried a query, but the
results are wrong, since I wrote 2 separate queries for each 1 of the
2 sums, and the results of the "combined' query (that I am trying to
put together) did not match it, i.e. the results were much, much
larger than they should haev been, leading me to think that the query
summed up the totals of both fields on both sums. Here is the query
that I used (there are times when only 1 ndc will be in 1 table, not
both, so I used the "COALESCE" statement to pick the ndc from the 1
table that it exists, for those cases. If there are such cases, I
would like for it to say "NULL" for the sum of the table that doesn't
contain an ndc):

SELECT COALESCE(PLANREBATE.NDC, CLIENTBACKUP.NDC) AS NDC,
SUM(PLANREBATE.AMOUNT) AS SUM_OF_AMOUNTS, SUM(CLIENTBACKUP.REBATE) AS
SUM_OF_REBATES FROM PLANREBATE FULL OUTER JOIN CLIENTBACKUP ON
PLANREBATE.NDC = CLIENTBACKUP.NDC GROUP BY PLANREBATE.NDC,
CLIENTBACKUP.NDC;

[Fred.]

I'd try an outer joint between two derived tables:

SELECT ISNULL(p.NDC, c.NDC) ndc_, sum_of_amounts, sum_of_rebates FROM
(SELECT NDC, SUM(AMOUNT) sum_of_amounts FROM PlanRebate
GROUP BY NDC) p
FULL OUTER JOIN
(SELECT NCC, SUM(REBATE) sum_of_rebates FROM ClientBackup
GROUP BY NDC) c
ON p.NDC=c.NDC
ORDER BY ISNULL(p.NDC, c.NDC)

However, this may take a while, and it could be faster to create two
temporary tables each with NDC as primary key and join them.

Fred.