Search code examples
mysqlcountsumsql-order-bysql-limit

Sql query find the car whcih is having maximim mileage

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
Solution

  • 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