PramTal PramTal - 2 months ago 11
SQL Question

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.

Answer

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
)