Used 4x4 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 join used_vehicles_bodystyle on used_vehicles.carID_int=used_vehicles_bodystyle.carID_int WHERE used_vehicles_bodystyle.4x4='x' AND large='x' group by manufacturer,model ORDER BY modelCount desc LIMIT 0,12