Issue with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Issue with query

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2013-11-24 : 08:48:30

Please help me with below

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

Db consist below tables. Key is model.

The database scheme consists of four tables:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

I tried below but iam geting two results

with a as (select maker,type,count(*) as number from product
group by maker,type)

select maker,count(*) from a

group by maker

table product contain

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-25 : 00:40:22

[code]
SELECT maker
FROM table
GROUP BY maker
HAVING MIN(type) = MAX(type)
AND MIN(model) <> MAX(model)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-25 : 00:40:28

select maker,type,count(*) as number
from product
group by maker,type
HAVING count(DISTINCT model ) >1

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-25 : 00:43:06

quote:
Originally posted by bandi

select maker,type,count(*) as number
from product
group by maker,type
HAVING count(DISTINCT model ) >1

--
Chandu

I cant see how this will ensure the maker has only one type associated to it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-25 : 01:14:20

quote:
Originally posted by visakh16

quote:
Originally posted by bandi

select maker,count(*) as number
from product
group by maker
HAVING count(DISTINCT model ) >1 AND count(DISTINCT type)=1

--
Chandu

I cant see how this will ensure the maker has only one type associated to it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources