当我使用 join -Postgresql 时，如何使用 count() 而不改变输出

Question

我需要找到汽车经销商所有租赁天数的总和(包括 period_begin和period_end，不是唯一的(该部门的所有汽车。除以该部门曾经拥有的不同(唯一(员工的总数。

我有5张桌子

Department(PK departmentnr, name, FK postcode, FK place_name) 
Employee(PK employeenr, FK email)
Contract(PK periode_begin, PK periode_end, FK departmentnr, FK employeenr)
registerform (,PK periode_end,PK,periode_end,FKemail,FK,
Fk numberplate,periode_end,periode_begin)
car(PK numberplate,FK departmentnr,FK Brand,FK model)

当我一步一步走

第 1 部分 每个部门的员工总数

select departmentnr,Count(employeenr)FROM contract 
group by departmentnr

第 2 部分 租用汽车的天数

SELECT DISTINCT departmentnr, 
Sum((( Date(periode_end) - Date(periode_begin) + 1 ))) AS 
average 
FROM   registerform r 
INNER JOIN car w using(numberplate) 
GROUP  BY  departmentnr

我得到了正确的输出，但是当我尝试将这两个放在一起时

SELECT distinct departmentnr, 
(
sum(((date(r.periode_end) - date(r.periode_begin) + 1))) / (
select
count(employeenr))
)
as average 
from
registerform r 
inner join
car w using(numberplate) 
inner join
contract using(departmentnr) 
inner join
employee using(employeenr) 
group by
departmentnr

然后我的输出变得荒谬。 我该如何解决这个问题，有没有办法使代码更有效率。

Answer 1

在JOIN之前聚合。 因此，一种方法是：

SELECT c.departmentnr, co.num_employees,
Sum( Date(r.periode_end) - Date(r.periode_begin) + 1 ) AS average 
FROM registerform r JOIN
car c
USING (numberplate) LEFT JOIN
(SELECT co.departmentnr, Count(*) as num_employees
FROM contract co 
GROUP BY co.departmentnr
) co
ON co.departmentnr = c.departmentnr
GROUP BY c.departmentnr, co.num_employees;