CODEWITHSUNDEEP
sql
Ableman
Ableman

Reputation: 49

SQL join and aggregate function

I require a query for the following statement:

Show the details of all staff who have booked more than 4 vehicle rentals for customers.

The schema is as follows:

Staff (staffID, firstname, lastname, xyz..)

Customers (customerID, firstname, lastname, xyz..)

Booking (bookingID, staffID, customerID, vehicleregistration, date)

Vehicle (vehicleregistration, make, model, xyz..)

Cheers!

Upvotes: 0

Views: 575

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

I gather that all Booking records are actual vehicle rentals for customers. So you want to count these per staff. per staff translates to GROUP BY staffid. To check aggregation results (the count) you'd use the HAVING clause.

select * 
from staff
where staffid in
(
  select staffid
  from booking
  group by staffid
  having count(*) > 4
);

As you see, there is no need to join anything, as you only want data from the staff table. The booking count is criteria that better belongs in the WHERE clause.

Just for completeness sake, here is the same with a join:

select s.* 
from staff s
join
(
  select staffid
  from booking
  group by staffid
  having count(*) > 4
) b on b.staffid = s.staffid;

I find this less readable, but some people may prefer it still. You'd use this if you wanted to show information from the aggregation in your results, in your case the count.

select s.*, b.bookings
from staff s
join
(
  select staffid, count(*) as bookings
  from booking
  group by staffid
  having count(*) > 4
) b on b.staffid = s.staffid;

Upvotes: 2

Mansoor
Mansoor

Reputation: 4192

SELECT staffID,COUNT(vehicleregistration) FROM Staff JOIN Booking ON Staff.staffID = Booking.staffID JOIN Vehicle ON Vehicle.vehicleregistration = Booking.vehicleregistration GROUP BY staffID HAVING COUNT(vehicleregistration) > 4

Upvotes: 0

Related Questions