开发者

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

开发者 https://www.devze.com 2022-12-19 14:20 出处:网络
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

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号