SQL 我如何求和不同的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4464946/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I SUM DISTINCT Rows?
提问by Russell Schutte
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.
我正在为需要对 DISTINCT 行求和的查询而苦苦挣扎。必须有办法做到这一点......但我迷路了。
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 的总和？
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
罗素舒特
回答by Robb
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
回答by Byron Whitlock
Use GROUPBY along with the SUM()and COUNT()aggregates.
将GROUPBY 与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.
根据您的数据库服务器，这将比执行子选择更有效。
回答by Zachary Scott
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”。Group by 与不同的字段具有相同的效果，但允许您使用聚合函数。您可以在 group by 后添加“Having”子句以过滤您想要查看的记录。
