Changes between Version 7 and Version 8 of AdvancedDatabaseReports

Timestamp:

12/20/22 12:38:27 (19 months ago)

Author:

181085

Comment:

--

AdvancedDatabaseReports

@@ -20 +20 @@
 
 {{{#!sql:
-SELECT dr.driver_id, dr.email, (CASE WHEN num_grades > 2 THEN AVG(de.grade) ELSE NULL END) as driver_grade, COUNT(*) as number_of_drives, 
+SELECT dr.driver_id, dr.email, c.make, c.model, (CASE WHEN num_grades > 2 THEN AVG(de.grade) ELSE NULL END) as driver_grade, COUNT(*) as number_of_drives, 
 SUM(p.total_sum_payed) as total_money_made, COUNT(DISTINCT de.request_id) as number_of_different_requests,
 COUNT(DISTINCT r.passenger_id) as number_of_different_passengers, (SUM(p.total_sum_payed))/COUNT(DISTINCT de.request_id) as average_money_per_request,
-SUM(d.km_travelled) as total_km_travelled
+SUM(de.km_travelled) as total_km_travelled
 FROM 
 driver dr
+LEFT JOIN car c ON dr.driver_id = c.driver_id 
 JOIN drive de ON dr.driver_id = de.driver_id 
 JOIN payment p ON de.drive_id = p.drive_id 
 LEFT JOIN request r ON de.request_id = r.request_id 
-GROUP BY dr.driver_id, dr.email 
+GROUP BY dr.driver_id, dr.email, c.make, c.model
 ORDER BY driver_grade DESC
 }}}