GROUP BY and COUNTs not producing expected results GROUP BY and COUNTs not producing expected results sql sql

GROUP BY and COUNTs not producing expected results


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 typeFROM   productGROUP  BY makerHAVING 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.


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)


SELECTDISTINCT maker, typeFROM makertypeGROUP BY makerHAVING COUNT(distinct type) = 1 ANDCOUNT(ident)> 1

You're trying to group by maker and type in the second query, which will isolate any combination of maker and type that has exactly one type (which will always be true, since each group contains one distinct pair of maker and type) and two or more models. You only want to group by maker.