我在查询需要在SUM DISTINCT Rows处苦苦挣扎。一定有办法做到这一点…但我迷路了。
这是我得到的:
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)
这给了我34行,为每个属于userid4的县提供了不同的人口数量,但是我怎么得到PopEstimate2005和EstimatesBase2000的总和?
类似的东西(但这不是合法的查询):
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
当然,将Zipcodes.CountyID添加到GroupBy的末尾后,我又回到了34行。
非常感谢您的帮助。
罗素·舒特(Russell Schutte)。。。。。
在获得以下帮助(尤其是Robb的帮助)之后,我能够获得我真正想要的内容-在单个查询中总共获得了每个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)
如果您只想要一个总体数字,请尝试
select sum(PopEstimate2005), sum(EstimatesBase2000) 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 WHERE (users_link_territory.userid = 4) ) as foo