I have 2 tables ZIPCROSS and HOUSEHOLDS. The fields for each are as follows:

ZIPCROSS      HOUSEHOLDS
--------      ----------
AREAID        ZIP
ZIP           TOTAL

ZIPCROSS holds zipcodes assigned for particular AreaID. HOUSEHOLDS contains TOTAL number of household in each zipcode.

Now, I need to build a query that returns SUM of TOTAL for a given AREAID grouped by SCF (first 3 numbers of the zipcode) and SUM of TOTAL for a given SCF. Thus the results should look something like this:

AREAID   SCF   TOTAL   SCFTOTAL
------   ---   ------- ---------
1        900   1234    43210
1        901   2345    54321

etc... I can write a query that can get the right TOTAL or the right SCFTOTAL but not both on one query. The following query gives me the right SCFTOTAL but not TOTAL.

SELECT A.AREAID, LEFT(C.ZIP,3) AS SCF, SUM(D.TOTAL) AS TOTAL, SUM(E.TOTAL) AS SCFTOTAL
FROM AREAORDER A JOIN ZIPCROSS C ON A.AREAID=C.AREAID
JOIN HOUSEHOLDDATA D ON C.ZIP=D.ZIP
JOIN HOUSEHOLDDATA E ON LEFT(C.ZIP,3)=LEFT(E.ZIP,3)
WHERE A.MAILINGORDERID=133
GROUP BY A.AREAID, LEFT(C.ZIP,3)
ORDER BY A.AREAID, SCF

I'm aware of why this doesn't work but I can't seem to find the right approach. Any solutions? TIA.