如何SUM 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.

我正在努力查询需要SUM DISTINCT Rows的查询。必须有办法做到这一点......但我迷路了。

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?

这给了我34行,为每个属于userid4的县提供不同的人口数量,但我如何得到PopEstimate2005和EstimatesBase2000的SUM?

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.

当然,只要我将Zipcodes.CountyID添加到GroupBy的末尾,我就会再次回到我的34行。

Thanks so much for any help.

非常感谢您的帮助。

Russell Schutte . . . . .

拉塞尔舒特。。。。。

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:

获得以下帮助后 - 特别是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

Thanks everyone who contributed!

谢谢大家的贡献!

Russell Schutte

3 个解决方案

#1

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

#2

An easy answer is use "group by". Group by has the same effect with the same fields as distinct, but allows you to use aggregate functions. You can add a "Having" clause after the group by to filter what records you would like to see.

一个简单的答案是使用“分组依据”。 Group by与distinct相同的字段具有相同的效果,但允许您使用聚合函数。您可以在分组后添加“Having”子句,以过滤您想要查看的记录。

#3

Use GROUP BY along with the SUM() and COUNT() aggregates.

使用GROUP BY以及SUM()和COUNT()聚合。

SELECT count(*) as totalRows, Zipcodes.CountyID,
   sum(us_co_est2005_allData.PopEstimate2005) as SumPopEstimate2005, 
   sum(us_co_est2005_allData.EstimatesBase2000) as SumEstimatesBase2000,
  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  Zipcodes.CountyID,users_link_territory.userID

Depending on your db server, this will be more efficient than doing a sub-select.

根据您的数据库服务器,这将比执行子选择更有效。

#1