GROUP BY and COUNTs not producing expected results

Question

I am doing some SQL exercises, but I'm stuck in the one I'll describe.

There is a table called product that has 3 columns: maker, model and type.

This is the result of select * from product

The excercise says this: Find out makers who produce only the models of the same type, and the number of those models exceeds 1. Deduce: maker, type.

The correct query should return this:

My approach was to first see which makers made only one type of product and then exclude those makers who have only one model. To do that I used the following query, and it returns the correct result, except for the fact that I can only manage to display the maker, but not the type and the exercise asks for both.

This is my query:

SELECT
DISTINCT maker
FROM product
GROUP BY maker
HAVING COUNT(distinct type) = 1 
AND
COUNT(model)> 1

And it returns this:

then, when I try to display the type as well by doing this:

SELECT
DISTINCT maker,type
FROM product
GROUP BY maker,type
HAVING COUNT(distinct type) = 1 
AND
COUNT(model)> 1

this is what I get:

Do you have any idea of why this is not working as expected? What would you do to solve this problem? I've been trying to solve this for over 3 hours with no success. :( Help me please.

Answer 1

If you are only returning those groups containing exactly one type you can just use MAX/MIN to find out what the type in that group is.

SELECT maker,
       MAX(type) AS type
FROM   product
GROUP  BY maker
HAVING COUNT(DISTINCT type) = 1
       AND COUNT(model) > 1 

When you add type into the GROUP BY list it will give you a result row for every combination of maker,type which is why your second attempt doesn't work.

Answer 2

SELECT DISTINCT maker, type 
FROM product 
WHERE maker IN 
   (SELECT DISTINCT maker 
    FROM product 
    GROUP BY maker HAVING COUNT(distinct type) = 1
    AND count(model) > 1)