如何对不同的行求和？

Question

我在查询需要在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）

Answer 1

如果您只想要一个总体数字，请尝试

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