TSQL: Group by query

Question

Hi Friends,

Table is Product and it has (maker, model, type) column. It is assumed that model numbers in the Product table are unique for all the makers and product types. Makers column has values A,B,C,D and Type column has values PC,Laptop, Printer.
Example

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 need to find out makers who produce only the models of the same type, and the number of those models exceeds 1.
Result should be

maker type
D Printer

Thanks in advance

parixit

Answer 1

This is relational division problem, try

select Maker, max(Type) as [Type]

from MakerModels

GROUP BY maker

HAVING MAX([Type]) = MIN([Type]) and COUNT([Model]) > 1

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

SELECT
	maker
FROM
	mytable
GROUP BY
	maker
HAVING
	count(*) > 1
	and count(distinct type) = 1
	

Answer 3

Hi Parixit

I think this is what you meant:

DECLARE @product table (maker CHAR(1), model INT, type VARCHAR(30))
INSERT INTO @product (maker,model,type) VALUES
 ('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')

select Maker, [type], COUNT(1) as [count]
from @product
GROUP BY maker, [type]
HAVING COUNT(1) > 1

---

Pérez

Yeah... misread... please ignore!