How to do SUM(VacancyID) without Duplicates while also showing Count(VacancyStartID) in the same Group?

Question

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:

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   

P.S. SUM(DISTINCT Number_of_vacancies) does NOT work either.

Note:These questions are not applicable in this instance:

http://stackoverflow.com/questions/2235922/how-to-do-sumvacancyid-without-duplicates-while-also-showing-countvacancystart

http://stackoverflow.com/questions/18932/sql-how-can-i-remove-duplicate-rows

http://stackoverflow.com/questions/1109640/how-do-i-remove-duplicate-rows-from-a-view

http://stackoverflow.com/questions/620471/using-multiple-counts-and-sums-in-a-single-sql-statement

Answer 1

This is how GROUP BY is supposed to work. This cannot be done in a single query.

By the way, what does SUM(VacancyID) mean? It seems to have no sense.