Changes between Version 15 and Version 16 of AdvancedDatabaseReports

Timestamp:

01/29/23 22:01:41 (16 months ago)

Author:

181085

Comment:

--

AdvancedDatabaseReports

@@ -13 +13 @@
 
 {{{#!sql:
-SELECT uuid_generate_v4() as id, pay.passenger_id, d.driver_id, SUM(d.km_travelled) as km_travelled_with_driver, 
+SELECT uuid_generate_v4() as id, pay.passenger_id, d.driver_id, dr.first_name as driver_name, dr.surname as driver_surname, SUM(d.km_travelled) as km_travelled_with_driver, 
 SUM(pay.total_sum_payed) as total_price_paid, SUM(pay.total_sum_payed)/SUM(d.km_travelled) as price_per_km
 FROM request r
 JOIN drive d ON r.request_id = d.request_id 
 JOIN payment pay ON d.drive_id = pay.drive_id 
-GROUP BY d.driver_id, pay.passenger_id 
+left join driver dr on d.driver_id = dr.driver_id 
+GROUP BY d.driver_id, dr.first_name, dr.surname, pay.passenger_id
 ORDER BY price_per_km asc
 }}}
@@ -25 +26 @@
 
 {{{#!sql:
-SELECT uuid_generate_v4() as id, dr.driver_id, dr.email, dr.first_name as driver_name, dr.surname as driver_surname, c.make, c.model, (CASE WHEN num_grades > 2 THEN AVG(de.grade) ELSE NULL END) as driver_grade, COUNT(*) as number_of_drives, 
+SELECT uuid_generate_v4() as id, dr.driver_id, dr.email as driver_email, dr.first_name as driver_name, dr.surname as driver_surname, c.make, c.model, (CASE WHEN num_grades > 2 THEN AVG(de.grade) ELSE 0 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,
@@ -36 +37 @@
 LEFT JOIN request r ON de.request_id = r.request_id 
 GROUP BY dr.driver_id, dr.email, dr.first_name, dr.surname, c.make, c.model
-ORDER BY driver_grade DESC
+ORDER BY driver_grade desc
 }}}
 
@@ -42 +43 @@
 
 {{{#!sql:
-SELECT p.passenger_id, p.email, SUM(d.km_travelled) as km_travelled_with_passenger, 
+SELECT uuid_generate_v4() as id, d.driver_id, p.passenger_id, p.email as passenger_email, p.first_name as passenger_name, p.surname as passenger_surname, SUM(d.km_travelled) as km_travelled_with_passenger, 
 SUM(pay.total_sum_payed) as total_earnings, SUM(pay.total_sum_payed)/SUM(d.km_travelled) as earnings_per_km, 
 AVG(d.grade) as average_grade_received_per_drive, COUNT(*) as number_of_drives
@@ -49 +50 @@
 JOIN drive d ON r.request_id = d.request_id 
 JOIN payment pay ON d.drive_id = pay.drive_id 
-WHERE d.driver_id = '7c183cbe-bfb3-4e16-b831-e4f73c4f4937' 
-GROUP BY p.passenger_id , p.email 
+GROUP BY p.passenger_id , p.email, p.first_name,  p.surname, d.driver_id 
 ORDER BY earnings_per_km DESC
 }}}