Alternative formulation

Question

Hi,

Is there a way that this query can be re-arranged as a composite query or not?

SELECT motor_car, COUNT(engine_type)
FROM ford_cars
GROUP BY motor_car
HAVING COUNT(engine_type) >= ALL
       (SELECT COUNT(engine_type)
        FROM ford_cars
             GROUP BY motor_car);

What the query is trying to achieve is to give the motor car and number of engine types for the car model on which the largest number of engine_types are available.

Many thanks Ben

Answer 1

Not sure what you are trying to achieve exactly, here's how to get the motor_car with the greatest number of different engine_type:

  SELECT motor_car, COUNT(DISTINCT engine_type) AS cnt
    FROM ford_cars
GROUP BY motor_car
ORDER BY cnt DESC
   LIMIT 1

Is that what you're looking for? If not, how does it differ from it?