CODEWITHSUNDEEP
sqlgroup-by
Alex Terreaux
Alex Terreaux

Reputation: 1941

GROUP BY and COUNTs not producing expected results

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

enter image description here

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:

enter image description here

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:

enter image description here

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:

enter image description here

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.

Upvotes: 10

Views: 8396

Answers (6)

DVRK
DVRK

Reputation: 11

Try this:

select maker, max(type) as type from Product
Group by maker
Having count (Distinct type) = 1 and Count(maker)>1

Upvotes: 0

sreeprasad
sreeprasad

Reputation: 3260

SELECT MAKER,MAX(TYPE)
 FROM PRODUCT
 GROUP  BY MAKER
 HAVING  COUNT(*)>1  AND  MAX(TYPE)=MIN(TYPE)

Upvotes: 0

dghelani
dghelani

Reputation: 47

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)

Upvotes: 5

SSC
SSC

Reputation: 1

    select distinct product.maker, product.type from product, (SELECT maker

    FROM product

    GROUP BY maker

    HAVING COUNT(distinct type) = 1 

           AND

           COUNT(model)> 1) as x

    where x.maker=product.maker

Try this. It worked for me.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453067

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.

Upvotes: 16

Jim Dagg
Jim Dagg

Reputation: 2032

SELECT
DISTINCT maker, type
FROM makertype
GROUP BY maker
HAVING COUNT(distinct type) = 1 
AND
COUNT(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.

Upvotes: 1

Related Questions