Used Hatchback Cars

select SQL_CALC_FOUND_ROWS used_vehicles.carID_int as id_int,manufacturer as manu,model as range,engine_size,derivative,transmission,additional_details,no_doors,fuel_type,body,min(deal_price) as price,max(deal_price) as maxprice,used_vehicles.carID,mileage,colour,image_url,reg_year,reg_letter,used_vehicles.userID,count(model) as modelCount FROM used_vehicles left join used_vehicles_lifestyle on used_vehicles.rancode=used_vehicles_lifestyle.rancode WHERE used_vehicles.body='hatchback' AND medium='x' group by manufacturer,model ORDER BY modelCount desc LIMIT 0,12