x

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

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) Find out makers who produce only the models of the same type, and the number of those models exceeds 1.
more ▼

asked Jan 09, 2013 at 05:27 PM in Default

indiver gravatar image

indiver
10 4 4 5

Is this a homework question?
Jan 09, 2013 at 08:00 PM JohnM
Do you mean those makers who, say, only produce Laptops?
Jan 09, 2013 at 10:22 PM ThomasRushton ♦
no...i mean the maker who only produce same type like either laptop or pc or printer...they only product only one type product .....and no of model of that type should be more than 1,
Jan 10, 2013 at 04:47 AM indiver
thanks.....i got result.... a lot of thanks....
Jan 11, 2013 at 05:14 AM indiver
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I'm still not sure I entirely understand what you're getting at. However, I've prepared this as a little demo of what I think you want...

So, here's a table for

use tempdb
go
create table Product (Maker varchar(10), Model varchar(20), ProductType char(1))
insert into Product values 
('Lenava', 'W510', 'L'), ('Lenava', 'W530', 'L'),
('PH', 'LT300', 'L'), ('PH', '4100N', 'P'), ('PH', 'LT200', 'L'),
 ('Dull', 'XPS17', 'L')

And here's the code that actually runs the query:

;WITH DistinctMakerProductType AS (
    select distinct maker AS Maker, ProductType AS ProductType
    FROM Product
) 
SELECT 
    Maker, ProductType, Count(*)
FROM Product
WHERE Maker IN (
        SELECT Maker FROM DistinctMakerProductType 
        GROUP BY Maker HAVING COUNT(*) = 1
        )
GROUP BY Maker, ProductType

The above query returns data from my Product Table showing Maker, Product Type and number of products that that maker makes, so long as the maker only makes one line of products.

The way it works:

  • The WITH clause just builds a distinct set of Makers & ProductTypes
  • the IN clause returns only Makers where there's only one ProductType in the "table" generated by the WITH clause
  • The rest is just a simple COUNT / GROUP BY but only working on the Makers returned by the previous step.
If you want it to only show Makers which make more than one item in a product line, then change the final GROUP BY to GROUP BY Maker, ProductType HAVING COUNT(*) > 1 (think of the HAVING bit as being like a WHERE clause for GROUP BY aggregates...)
more ▼

answered Jan 10, 2013 at 09:11 PM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x346

asked: Jan 09, 2013 at 05:27 PM

Seen: 1544 times

Last Updated: Jan 11, 2013 at 05:14 AM