I'm struggling with a query where I need to SUM DISTINCT Rows. There has to be a way to do this... but I'm lost.
Here's what I've got:
SELECT DISTINCT Zipcodes.CountyID,
us_co_est2005_allData.PopEstimate2005,
us_co_est2005_allData.EstimatesBase2000,
users_link_territory.userID
FROM
Zipcodes Inner Join Users_link_territory ON zipcodes.CountyID =
Users_link_territory.CountyID Inner Join
us_co_est2005_alldata ON zipcodes.FIPS = us_co_est2005_alldata.State AND zipcodes.code
= us_co_est2005_alldata.County
WHERE (users_link_territory.userid = 4)
This gives me the 34 rows which provide distinct population numbers for each county belonging to userid4, but how would I get the SUM of PopEstimate2005 and EstimatesBase2000?
Something like (but this isn't a legal query):
SELECT DISTINCT Zipcodes.CountyID,
SUM(us_co_est2005_allData.PopEstimate2005) AS Population2005,
SUM(us_co_est2005_allData.EstimatesBase2000) AS Population2000,
users_link_territory.userID
FROM
Zipcodes Inner Join Users_link_territory ON zipcodes.CountyID =
Users_link_territory.CountyID Inner Join
us_co_est2005_alldata ON zipcodes.FIPS = us_co_est2005_alldata.State AND zipcodes.code
= us_co_est2005_alldata.County
WHERE (users_link_territory.userid = 4)
GROUP BY users_link_territory.userid
Of course, as soon as I add Zipcodes.CountyID to the end of the GroupBy, I'm back with my 34 rows again.
Thanks so much for any help.
Russell Schutte . . . . .
Получив помощь ниже - в частности, помощь Робба - я смог получить то, что действительно хотел - общую информацию о населении каждого UserID в одном запросе:
SELECT SUM(POPESTIMATE2005) AS Expr1, SUM(ESTIMATESBASE2000) AS Expr2, UserID
FROM (
SELECT DISTINCT zipcodes.CountyID, us_co_est2005_alldata.POPESTIMATE2005, us_co_est2005_alldata.ESTIMATESBASE2000, users_link_territory.UserID
FROM zipcodes INNER JOIN
users_link_territory ON zipcodes.CountyID = users_link_territory.CountyID INNER JOIN
us_co_est2005_alldata ON zipcodes.FIPS = us_co_est2005_alldata.STATE AND zipcodes.Code = us_co_est2005_alldata.COUNTY
) As FOO
GROUP BY UserID
Спасибо всем, кто внес свой вклад!
Russell Schutte