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

Question

In SSRS 2005 I am reporting 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. Note:These questions are not applicable in this instance: 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

If it's in the Underlying SQL Server call...

You can do ...SUM(DISTINCT VacancyID)... like you can COUNT (DISTINCT ..)

Edit:

SELECT
    col1, col2, SUM(DISTINCT Number_of_vacancies)  as foo, COUNT (VacancyStartID) as bar
FROM
    MyView
...

If it's in the table or for a cell in the report, then there is no equivalent in the SSRS SUM function.

Answer 2

Do some grouping already in your query and then make a group with a simple Count in SSRS.