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;