我如何对不同的行求和? [英] 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.
非常感谢您的帮助.
罗素舒特.....
在获得以下帮助(尤其是 Robb 的帮助)后,我能够获得我真正想要的东西 - 在单个查询中获得每个 UserID 的总体详细信息:
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
感谢所有贡献的人!
罗素舒特
推荐答案
如果你只是想要一个整体的数字试试
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屋!