I am trying to find car dealerships that only sell cars of one type. I have a table car containing the carid, the dealership where the car is, the engine the car has and what type the car is (which can only be one of 4 types: sedan,coupe,hatchback,sports).

I know how to group by dealerships but I don't understand how to only find dealerships that have cars of only one type. Here is what I have so far:

select carid,dealership,type from car group by dealership

Now my inital idea was to group by both dealership and type but that didn't find dealerships with cars of only one type as it gave me multiple results for some dealerships.

Then I tried to add a separate having clause like having type='sedan' for each type and then make a union of all the tables but that gave the same thing as the idea to group by both fields.

CodePudding user response：

Just use Having to check the equality of the maximum and minimum values.

select dealership, Max(type) 
from car 
group by dealership
Having Max(type)=Min(type)