Finding the average rating of booking from each driver in a month in SQL

Question

I'm currently taking the online class on databases, If you could help me solve this sql problem I would greatly appreciate it. Sorry I'm a complete noob.

I need to list out all the drivers’ name, age, position and their average rating for a particular month. Listing out name,age and position is ok but i have problem with the average rating(as there are few entries for a 'booking',need to calculate average from those entries) and also group them into each driver from a particular month

below are my codes

  table booking
  BookID NUMBER(4) PRIMARY KEY,
  PersonID NUMBER(4),
  driverID NUMBER(4),    
  PickLoc VARCHAR2(13),
  DropLoc VARCHAR2(13),
  TDate DATE,
  TTime NUMBER(4),
  RideFare CHAR(6),
  TollOther CHAR(6),
  cancelDate DATE,
  TripRating NUMBER(1), 
  PayMethod CHAR(15),



  table driver
  driverID NUMBER(4) PRIMARY KEY, 
  Dname VARCHAR2(20),
  DNRIC CHAR(14),
  Dgender CHAR(1),
  DDOB DATE,
  Dmobile CHAR(11),
  DcarNo CHAR(6),
  Dstart DATE,
  Dgrade CHAR(6),
  DLicence CHAR(4),
  );

Answer 1

This is a simple group by and apply the avg (average) function.

select Dname, timestampdiff(year,Ddob,Now()) as age, 
       Dgrade, avg(TripRating) as rate
from booking
     inner join driver on driver.driverID = booking.driverID
where extract(year from TDate) = 2019 and
      extract(month from TDate) = 1
group by Dname, timestampdiff(year,Ddob,Now()), Dgrade

It will return your averages for January 2019

PS: As @RohanKumar made me notice, my answer won't return any result for Drivers without a Booking on that period. If you want the result set to have all the drivers, even the ones without bookings, then we change the order of joining, using Driver as the main table, and changing the join for an outer join, so we return Drivers without bookings.

select Dname, timestampdiff(year,Ddob,Now()) as age, 
       Dgrade, avg(TripRating) as rate
from driver
     left join booking on booking.driverID = driver.driverID
where extract(year from TDate) = 2019 and
      extract(month from TDate) = 1
group by Dname, timestampdiff(year,Ddob,Now()), Dgrade

Answer 2

That isn't so hard at all! You just need to join the tables, group by driver ID, then just use AVG() function method!

SQL:

SELECT Dname as driver, 
       TIMESTAMPDIFF(YEAR, DDOB, CUR_DATE()) as age,
       Dgrade as position,
       rating
FROM driver 
LEFT JOIN 
(SELECT driverID, AVG(TripRating) as rating
 FROM booking group by driverID, MONTH(TDate)
) booking
on driver.driverID = booking.driverID

This will list out all Driver Ratings for all months, for any given month simply change the inner SQL of booking table.

That change would be so for January (Month 1):

SELECT driverID, avg(TripRating) as rating
 FROM booking group by driverID
where MONTH(TDate) = 1

helpful references

• https://dev.mysql.com/doc/refman/8.0/en/date-calculations.html
• Using group by on multiple columns

Answer 3

first you have to join your tables and select the columns you need for your calculations:

SELECT Dname, Triprating, DDOB, TDate 
FROM  driver, booking 
WHERE booking.driverID = driver.driverID;

the drivers age is a bit tricky. According to https://stackoverflow.com/a/23824981/2331592 this could look like (assuming that you want the age at the time of the booking)

TIMESTAMPDIFF(YEAR, TDate , DDOB)

also the booking months: I would suggest to also take the year into account, maybe by formating it into '2019-06' by

DATE_FORMAT(TDate ,'%Y-%m')

so the query would now lokk like

SELECT Dname, TIMESTAMPDIFF(YEAR, TDate , DDOB) AS Dage, DATE_FORMAT(TDate ,'%Y-%m') AS Tmonth,  Triprating 
FROM  driver, booking 
WHERE booking.driverID = driver.driverID;

Now this is a table with all information and we want to group some things.
For this we need aggregate functions for the fields we do not want to group on.

I use the previous query as a sub query here. But also could be done in a single one but so it is more clear.

SELECT  Dname as driver, 
        MAX(Dage) as age, 
        Tmonth as month, 
        AVG(Triprating) as 'average rating'
FROM    (SELECT Dname, 
                TIMESTAMPDIFF(YEAR, TDate , DDOB) AS Dage, 
                DATE_FORMAT(TDate ,"%Y-%m") AS Tmonth,  
                Triprating 
         FROM   driver, booking 
         WHERE  booking.driverID = driver.driverID
        )
GROUP BY driver, month
ORDER BY driver ASC, month ASC;

with light adaptions to SQLite the above SQL works and can be tried at: https://sqliteonline.com/#fiddle-5d04d5f898b5cmzxjwxfrdws

Edit:

as Rohan Kumar in his answer suggests, the grouping of the booking data could be done before the join. this would reduce the number of rows to join and may a bit faster and less resource consuming. But in this variant the age of the driver at the booking time can not be calculated that easy.