Search code examples
mysqlcountsubquerymaxhaving-clause

Having With Sub query MySql

I want to select which model car is involved in most accidents. i am using the following query but i get a syntax error. please someone tell me whats wrong..

    select car.Model 
    from car 
    join car_accident_involved 
       on car.Car_Registration_ID = car_accident_involved.Car_Registration_ID 
    group by car.Model 
    having MAX(
       select COUNT(Car_Registration_ID) 
       from car_accident_involved
    );
Solution

  • You can use a simple sub query here, e.g:

    select model from car
where car_registration_id = 
 (select car_registration_id
  from car_accident_involved
  group by model 
  order by count(car_registration_id) desc
  limit 1);