How to use count() without the output changing when I use join -Postgresql

Question

I need to find the sum of all the rental days of a car dealership (including period_begin and period_end, not unique) for all cars of that department.Divided by the total number of different (unique) employees that department ever had.

I have 5 tables

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)

when I go step by step

part 1 The total employees per department

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

part 2 the amount of days the cars were hired

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

I get the correct ouput but when I try to get these 2 together

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

then my output gets absurd. How can I fix this and is there a way to make the code more efficient.

Gordon Linoff · Accepted Answer · 2019-12-13 02:52:35Z

1

Aggregated before you JOIN. So, one method is:

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;

answered Dec 13, 2019 at 2:52

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use count() without the output changing when I use join -Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related