Double summation?

[John K.]

I have 2 tables ZIPCROSS and HOUSEHOLDS. The fields for each are as follows:
<PRE>
ZIPCROSS HOUSEHOLDS
-------- ----------
AREAID ZIP
ZIP TOTAL
</PRE>
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:
<PRE>
AREAID SCF TOTAL SCFTOTAL
------ --- ------- ---------
1 900 1234 43210
1 901 2345 54321
</PRE>
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&#39;m aware of why this doesn&#39;t work but I can&#39;t seem to find the right approach. Any solutions? TIA.

[Bill]

John --

The easiest way I can think of to do this in a single select statement is to join two derived tables. Here&#39;s a simple example that you can embellish:
Watch out for any &#34;total&#34; keywords...you may have to surround with brackets example t1.total should be t1.[total]

select t1.areaid,t1.scf,t1.total,t2.scftotal from
(select areaid,scf,total=sum(total) from YourTables group by areaid,scf) t1
inner join
(select scf,scftotal=sum(total) from YourTables group by scf) t2
on t1.scf=t2.scf
order by
t1.areaid,
t1.scf


------------
John K. at 5/23/2002 2:27:22 PM


I have 2 tables ZIPCROSS and HOUSEHOLDS. The fields for each are as follows:
<PRE>
ZIPCROSS HOUSEHOLDS
-------- ----------
AREAID ZIP
ZIP TOTAL
</PRE>
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:
<PRE>
AREAID SCF TOTAL SCFTOTAL
------ --- ------- ---------
1 900 1234 43210
1 901 2345 54321
</PRE>
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&#39;m aware of why this doesn&#39;t work but I can&#39;t seem to find the right approach. Any solutions? TIA.