summing population by zip and by county together

R

rpw

Hi everyone,

Hmmm, how do I describe this? Springfield-1 and Springfield-2 are
"territory" names. Each of the 'Springfield' territories are defined
geographically by a combination of Counties and Zip Codes.

(e.g. Chester county is in Springfield-1, Harris county is in Springfield-2,
and Springfield county is split between both territories with zip code 12345
belonging to Springfield-1 and 12399 belonging to Springfield-2, and so on.)

I have a table for territories by county, a table for territories by zip
code, at table for demographics by county and one for demographics by zip
code.

I have created two queries: one for summing/grouping by territory out of the
'by county' tables and one for summing/grouping by territories (not in the
county territories) out of the 'by zip code' tables. Both deliver the
expected results.

My question is: How can I get the results of each query grouped together
like the sample output below?

Territory Population Income WeightedIncome
Springfield-1 1,200,356 $4564216 $45563588494
Springfield-2 2,456,548 $4568716 $48979683413

Here's the SQL for the two queries:

SELECT tblTerritoryByCounty.Territory, tblTerritoryByCounty.Franchisee,
Sum(tblPopByCounty.[2007 Estimated Population]) AS [SumOf2007 Estimated
Population], Sum(tblPopByCounty.[2007 Estimated Median HH Income]) AS
[SumOf2007 Estimated Median HH Income], Sum([2007 Estimated Population]*[2007
Estimated Median HH Income]) AS GrossIncome
FROM tblPopByCounty INNER JOIN tblTerritoryByCounty ON (tblPopByCounty.STATE
= tblTerritoryByCounty.State) AND (tblPopByCounty.County =
tblTerritoryByCounty.County)
GROUP BY tblTerritoryByCounty.Territory, tblTerritoryByCounty.Franchisee
HAVING ((Not (tblTerritoryByCounty.Franchisee)="Z-None"));

SELECT Count(tblPopByZipCode.STATE) AS CountOfSTATE,
Count(tblZipCodesByTerritory.County) AS CountOfCounty,
Count(tblZipCodesByTerritory.ZipCode) AS CountOfZipCode,
tblZipCodesByTerritory.Territory, Sum(tblPopByZipCode.POPULATION) AS
SumOfPOPULATION, Sum(tblPopByZipCode.INCOME) AS SumOfINCOME,
Sum([POPULATION]*[INCOME]) AS WeightedIncome,
tblZipCodesByTerritory.[CountByZip&County]
FROM tblPopByZipCode INNER JOIN tblZipCodesByTerritory ON
tblPopByZipCode.ZIPCODE = tblZipCodesByTerritory.ZipCode
GROUP BY tblZipCodesByTerritory.Territory,
tblZipCodesByTerritory.[CountByZip&County]
HAVING (((tblZipCodesByTerritory.[CountByZip&County])=True));

All responses are appreciated... Thanks in advance for you help and advice.
 
M

Michel Walsh

If you use MS SQL Server, use a ROLLUP (or a CUBE). With Jet, proceed with
an overlapping 'catch all/catch nothing'. Better to illustrate the technique
with an example. It is easy, no need to have a PhD.


Let us assume we start with:

TheData
Amount ZipCode ItemName
1 aaa ia
2 bbb ib
5 ccc ic
7 ddd id




where ZipCode have to be regrouped by city, accordingly to:


CityZipCodes
ZipCode City
aaa A
bbb B
ccc A
ddd A
aaa

bbb

ccc

ddd




and the Items have to be regrouped by Utility:


UtilityItems
ItemName Utility
ia UtilityA
ib UtilityA
ic UtilityB
id UtilityB
ia

ib

ic

id






Note that the ZipCode and the Utility are listed twice, once with their real
matching super-group, and once associated with a 'catch-all/catch-nothing'
NULL. That null value will do just all the trick, ***automatically***, for
us.


In fact, it is just a matter to have the query:


SELECT Sum(TheData.Amount) AS SumOfAmount,
CityZipCodes.City,
UtilityItems.Utility

FROM (TheData INNER JOIN CityZipCodes
ON TheData.ZipCode = CityZipCodes.ZipCode)
INNER JOIN UtilityItems
ON TheData.ItemName = UtilityItems.ItemName

GROUP BY CityZipCodes.City, UtilityItems.Utility;




which is simply an inner join between the table with the data and with each
of the translation table, and here you go, you have your CUBE:


Query3
SumOfAmount City Utility
$15.00


$3.00
UtilityA
$12.00
UtilityB
$13.00 A

$1.00 A UtilityA
$12.00 A UtilityB
$2.00 B

$2.00 B UtilityA




Just a note on how to read it: here, a NULL, in that result, means 'for
all'.

So, first line, 15 is the sum for all cities, all utilities.
The second line says the sum is 3 for UtilityA, all cities.
The fourth line says the sum is 13 for City A, all utilities.



Hoping it may help,
Vanderghast, Access MVP





rpw said:
Hi everyone,

Hmmm, how do I describe this? Springfield-1 and Springfield-2 are
"territory" names. Each of the 'Springfield' territories are defined
geographically by a combination of Counties and Zip Codes.

(e.g. Chester county is in Springfield-1, Harris county is in
Springfield-2,
and Springfield county is split between both territories with zip code
12345
belonging to Springfield-1 and 12399 belonging to Springfield-2, and so
on.)

I have a table for territories by county, a table for territories by zip
code, at table for demographics by county and one for demographics by zip
code.

I have created two queries: one for summing/grouping by territory out of
the
'by county' tables and one for summing/grouping by territories (not in the
county territories) out of the 'by zip code' tables. Both deliver the
expected results.

My question is: How can I get the results of each query grouped together
like the sample output below?

Territory Population Income WeightedIncome
Springfield-1 1,200,356 $4564216 $45563588494
Springfield-2 2,456,548 $4568716 $48979683413

Here's the SQL for the two queries:

SELECT tblTerritoryByCounty.Territory, tblTerritoryByCounty.Franchisee,
Sum(tblPopByCounty.[2007 Estimated Population]) AS [SumOf2007 Estimated
Population], Sum(tblPopByCounty.[2007 Estimated Median HH Income]) AS
[SumOf2007 Estimated Median HH Income], Sum([2007 Estimated
Population]*[2007
Estimated Median HH Income]) AS GrossIncome
FROM tblPopByCounty INNER JOIN tblTerritoryByCounty ON
(tblPopByCounty.STATE
= tblTerritoryByCounty.State) AND (tblPopByCounty.County =
tblTerritoryByCounty.County)
GROUP BY tblTerritoryByCounty.Territory, tblTerritoryByCounty.Franchisee
HAVING ((Not (tblTerritoryByCounty.Franchisee)="Z-None"));

SELECT Count(tblPopByZipCode.STATE) AS CountOfSTATE,
Count(tblZipCodesByTerritory.County) AS CountOfCounty,
Count(tblZipCodesByTerritory.ZipCode) AS CountOfZipCode,
tblZipCodesByTerritory.Territory, Sum(tblPopByZipCode.POPULATION) AS
SumOfPOPULATION, Sum(tblPopByZipCode.INCOME) AS SumOfINCOME,
Sum([POPULATION]*[INCOME]) AS WeightedIncome,
tblZipCodesByTerritory.[CountByZip&County]
FROM tblPopByZipCode INNER JOIN tblZipCodesByTerritory ON
tblPopByZipCode.ZIPCODE = tblZipCodesByTerritory.ZipCode
GROUP BY tblZipCodesByTerritory.Territory,
tblZipCodesByTerritory.[CountByZip&County]
HAVING (((tblZipCodesByTerritory.[CountByZip&County])=True));

All responses are appreciated... Thanks in advance for you help and
advice.
Click to expand...
 
M

Michel Walsh

TheData
Amount ZipCode ItemName
1 aaa ia
2 bbb ib
5 ccc ic
7 ddd id



CityZipCodes
ZipCode City
aaa A
aaa

bbb B
bbb

ccc A
ccc

ddd A
ddd




UtilityItems
ItemName Utility
ia UtilityA
ib UtilityA
ic UtilityB
id UtilityB
ia

ib

ic

id





Query(the CUBE) SumOfAmount City Utility
$15.00


$3.00
UtilityA
$12.00
UtilityB
$13.00 A

$1.00 A UtilityA
$12.00 A UtilityB
$2.00 B

$2.00 B UtilityA
 
M

Michel Walsh

TheData
Amount ZipCode ItemName
1 aaa ia
2 bbb ib
5 ccc ic
7 ddd id



UtilityItems
ItemName Utility
ia UtilityA
ib UtilityA
ic UtilityB
id UtilityB
ia

ib

ic

id




CityZipCodes
ZipCode City
aaa A
aaa

bbb B
bbb

ccc A
ccc

ddd A
ddd






Query (a Cube)
SumOfAmount City Utility
$15.00


$3.00
UtilityA
$12.00
UtilityB
$13.00 A

$1.00 A UtilityA
$12.00 A UtilityB
$2.00 B

$2.00 B UtilityA
 
Top