CODEWITHSUNDEEP
sqldatabasepostgresql
Jan De Wolf
Jan De Wolf

Reputation: 23

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

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.

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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;

Upvotes: 1

Related Questions