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_vacanci开发者_JAVA技巧es) does NOT work either.
Note:These questions are not applicable in this instance:
How to do SUM(VacancyID) without Duplicates while also showing Count(VacancyStartID) in the same Group?
How can I remove duplicate rows?
How do I remove "duplicate" rows from a view?
Using multiple COUNTs and SUMs in a single SQL statement
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.
精彩评论