title: How to do SUM(VacancyID) without Duplicates while also showing Count(VacancyStartID) in the same Group?
body: <p>In SQL 2005 I am grouping on all available posts by regional office, listed by region,office,vacancyID. I then display a total per office on how many people started in a particular vacancyID by doing a Count(VacancyStartID). In the same group row with the Count(VacancyStartID) I need to display SUM(VacancyID). However at present this does not give the correct SUM, because some vacancies have multiple VacancyStartID's and hence the vacancyID is listed few times, like so:</p>
  
  <pre><code>office vacancyID Number_of_vacancies       VacancyStartID (person who started a job) 
  
   1         1              2                4567 
  
   1         1              2                5678 
  
  
  Totals:                   4 (needs to be 2)    2   
  </code></pre>
  
  <p>P.S. SUM(DISTINCT Number_of_vacancies) does NOT work either.</p>
  
  <p>Note:These questions are not applicable in this instance:</p>
  
  <p><a href="https://stackoverflow.com/questions/2235922/how-to-do-sumvacancyid-without-duplicates-while-also-showing-countvacancystart">How to do SUM(VacancyID) without Duplicates while also showing Count(VacancyStartID) in the same Group?</a></p>
  
  <p><a href="https://stackoverflow.com/questions/18932/sql-how-can-i-remove-duplicate-rows">How can I remove duplicate rows?</a></p>
  
  <p><a href="https://stackoverflow.com/questions/1109640/how-do-i-remove-duplicate-rows-from-a-view">How do I remove &quot;duplicate&quot; rows from a view?</a></p>
  
  <p><a href="https://stackoverflow.com/questions/620471/using-multiple-counts-and-sums-in-a-single-sql-statement">Using multiple COUNTs and SUMs in a single SQL statement</a></p>