Ableman Ableman - 23 days ago 9
SQL Question

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!

Answer

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 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;
Comments