CODEWITHSUNDEEP
sql
PramTal
PramTal

Reputation: 51

SQL group by and having clause issue wrt count

I have a below table Product maker model type

A      1232  PC
A      1233  PC 
A      1276  Printer
A      1298  Laptop
A      1401  Printer
A      1408  Printer
A      1752  Laptop
B      1121   PC 
B      1750  Laptop
C      1321  Laptop
D      1288  Printer
D      1433  Printer
E      1260  PC
E      1434  Printer
E      2112  PC
E      2113  PC

I want to find out the makers who produce only one product type and more than one model.

The answer is

Maker Type
D        Printer

So far, I have written this query:

Select maker, type 
from product  
group by type, maker 
having count(model) > 1

It gives the below result:

maker type

A     Laptop
A     PC
A     Printer
D     Printer
E     PC

My question is how to write condition to get result for 1 type only ? as writing having count(type)=1 removes maker D which is required.

Upvotes: 0

Views: 72

Answers (3)

PramTal
PramTal

Reputation: 51

SELECT distinct maker, type from product 
group by maker having count(distinct model)>1 AND 
count(distinct type) = 1;

Upvotes: 0

J. Bloggs
J. Bloggs

Reputation: 58

How about this:

SELECT DISTINCT p1.Maker, p1.[Type]
FROM product p1
JOIN 
(
  Select maker
  from product  
  group by maker 
  having count(distinct model) > 1    
  AND count(distinct [type]) = 1
) p2
ON p1.MAKER = p2.MAKER

Or:

SELECT DISTINCT p1.Maker, p1.[Type]
FROM product p1
WHERE maker in
(
  Select maker
  from product  
  group by maker 
  having count(distinct model) > 1    
  AND count(distinct [type]) = 1
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

How about this?

Select maker, max(type) as type
from product  
group by maker 
having min(type) = max(type) and
       min(model) <> max(model);

Upvotes: 1

Related Questions