CODEWITHSUNDEEP
sql-server-2000
Venaikat
Venaikat

Reputation: 197

Grouping and summarizing values

Find out makers who produce only the models of the same type, and the number of those models exceeds 1. 

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

The result should be 

 Model  Type   
  D      Printer

I tried 

   Select maker,type from Product
   Group by maker ,type 
   Having count(*)>1

The above query gives me the maker whose number of model exceeds one .But im not able to find the model which only produces same type .Please help

Upvotes: 0

Views: 417

Answers (1)

Ciarán
Ciarán

Reputation: 3057

Select Distinct Maker, Type From Product
 Where Maker In (Select Maker From Product 
                  Group By Maker 
                 Having Count(Distinct Type) = 1 
                    And Count(Distinct Model) > 1)

Upvotes: 2

Related Questions