Sql query find the car whcih is having maximim mileage

Question

find the car whcih is having maximum mileage

id,car,timestamp,mileage
101,audi,10/10/10 9:05:02,10.5
101,audi,10/10/10 9:07:02,10
101,audi,10/10/10 9:14:02,9
102,benz,10/10/10 8:24:02,8
102,benz,10/10/10 8:34:02,7
102,benz,10/10/10 8:44:02,6

Expected Out

audi =(10.5+10+9)/3 = 9.83
benz=(8+7+6)/3= 7

(1) What is your expected result? (2) What have you tried? – GMB Dec 04 '19 at 17:06 — GMB, Dec 04 '19 at 17:06

score 0 · Accepted Answer · answered Dec 04 '19 at 17:40

You seem to be looking for aggregation:

select 
    car,
    sum(mileage) / count(*) res
from mytable
group by id, car

If you want to retain only the car with the greatest result, then you can use order by and limit:

select 
    car,
    sum(mileage) / count(*) res
from mytable
group by id, car
order by res desc
limit 1

Sql query find the car whcih is having maximim mileage

1 Answers1