You can use either correlated query or derived table.

SELECT a.areaid, Left(a.zip,3) AS scf, SUM(h.total) AS scftotal, 
	( SELECT SUM(h1.total) 
		FROM householddata h1 
		JOIN areaorder a1 ON h1.zip = a1.zip
		WHERE a1.areaid = a.areid ) AS Total
	FROM householddata h 
	JOIN areaorder a ON h.zip = a.zip
	GROUP BY a.areaid, Left(a.zip,3)

SELECT a.areaid, Left(a.zip,3) AS scf, 
          SUM(h.total) AS scftotal, t1.total
	FROM householddata h 
	JOIN areaorder a ON h.zip = a.zip
	JOIN ( SELECT a1.areaid, SUM(h1.total) AS total 
		FROM householddata h1 
		JOIN areaorder a1 ON h1.zip = a1.zip
                GROUP BY a1.areaid) t1 ON t1.areaid = a.aread 
	GROUP BY a.areaid, Left(a.zip,3), t1.total		

>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.