如何对不同的行求和？ [英] How do I SUM DISTINCT Rows?

问题描述

我在查询需要加总DISTINCT行的查询时遇到了麻烦。必须有一种方法来做...但是我迷路了。

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.

这里是我所拥有的：

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的总和？

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

当然，我将Zipcodes.CountyID添加到GroupBy的末尾后，我又返回了34行。

Of course, as soon as I add Zipcodes.CountyID to the end of the GroupBy, I'm back with my 34 rows again.

非常感谢您的帮助。

Russell Schutte
。
。
。
。
。

Russell Schutte . . . . .

在获得以下帮助之后-特别是Robb的帮助-我能够得到我真正想要的-每个用户ID的人口详细信息总计单个查询：

After getting the below help - in particular Robb's help - I was able to get what I really wanted - a total of each UserID's population details in a single query:

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

Russell Schutte

推荐答案

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

If you just want an overall figure for it try

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

这篇关于如何对不同的行求和？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！