0

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
    );
Hunter McMillen
  • 59,865
  • 24
  • 119
  • 170
Mehroz Irshad
  • 287
  • 1
  • 5
  • 14
  • Do you want to know what's causing the syntax error, or what's wrong with the query? – Strawberry Mar 26 '16 at 22:57
  • If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Mar 26 '16 at 23:01

2 Answers2

1

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);
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
1

HAVING is a condition statement for GROUP BY. Your query hasn't any condition in
HAVING statement,so error arises. A for me, there is no need in subquery. Try more simple query like: 

SELECT c.model,COUNT(a.car_registration_id) AS Num_of_accidents FROM car c   
INNER JOIN car_accident_involved a ON c.car_registration_id=a.car_registration_id  
GROUP BY c.model ORDER BY Num_of_accidents DESC LIMIT 1;
jvb
  • 61
  • 6