title: How do I SUM DISTINCT Rows?
body: <p>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.</p>
  
  <p>Here's what I've got:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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?</p>
  
  <p>Something like (but this isn't a legal query):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Of course, as soon as I add Zipcodes.CountyID to the end of the GroupBy, I'm back with my 34 rows again.</p>
  
  <p>Thanks so much for any help.</p>
  
  <p>Russell Schutte
  .
  .
  .
  .
  .</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Thanks everyone who contributed!</p>
  
  <p>Russell Schutte</p>