GROUP BY and COUNTs not producing expected results
If you are only returning those groups containing exactly one type you can just use
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.
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.