Click here to Skip to main content
15,069,297 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am just starting SQL and I have been stuck on this for few hours.
I have two tables SERVICE and RUNNING, SERVICE table stores (car_id, service date) of multiple cars, one car can have multiple entries, RUNNING table stores (date, car_id, kms of running). I have to calculate the average running of cars after the last servicing.

And also how can I cap the maximum number of days, using max() function doesn't work?

What I have tried:

I am trying this, it's not working.

select r.car_id, sum(r.run)/count(distinct(r.date)) as avg_run from RUNNING r where r.date >= (select date from SERVICE s where s.car_id = r.car_id order by date desc limit 1) group by car_id;
Posted
Updated 15-Jul-21 4:03am

1 solution

Start by doing it in segments: Write a SELECT with a GROUP BY that returns the car ID and the last service date.

When that works, use the car_id and last service date to get the mileage With a JOIN back to the Service tabel to get you the ID, date, and mileage.

When that works, write a JOIN that combines that with your Running table and return the date, car_id, KMs, last service date and last service mileage.

When that works, it's trivial to get the average!
   
Comments
on_my_way 15-Jul-21 10:44am
   
Thank you very much :)
OriginalGriff 15-Jul-21 11:13am
   
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900