Reputation: 6872
I got this SQL query exercise from SQL-EX.RU
Find out makers who produce only the models of the same type, and the number of those models exceeds 1. Deduce: maker, type
Table info:
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)
The version I wrote (by scratching on my jotta pad first lolz):
SELECT Product.maker, Product.type
FROM Product
WHERE Product.maker IN
(SELECT X.maker
FROM
(SELECT DISTINCT maker,type
FROM Product
) AS X
GROUP BY X.maker
HAVING COUNT(X.type) = 1
)
GROUP BY Product.maker,Product.type
HAVING COUNT(Product.model) > 1
This gives me the correct result. However, I believe this is probably not the only solution (not the ideal solution, at the least) - because I am not an SQL wizard.
I would appreciate any explanation with the answers, but in case I simplify it better, I will update the question with the new query statement.
Ta
UPDATE Since the inital posting I summarised it into this:
SELECT maker, type
FROM Product
WHERE Product.maker IN
(
SELECT maker
FROM Product
GROUP BY maker
HAVING COUNT(DISTINCT type)=1
)
GROUP BY maker, type
HAVING COUNT(product.model) > 1
Upvotes: 0
Views: 64
Reputation: 35790
It can be simplified:
SELECT maker ,
MAX(type) AS type
FROM product
GROUP BY maker
HAVING COUNT(DISTINCT type) = 1
AND COUNT(*) > 1
Here you are grouping results by maker, and you want only those groups where distinct type count is 1 and count of total rows in group is greater then 1.
You could write:
HAVING COUNT(DISTINCT type) = 1
AND COUNT(DISTINCT model) > 1
But it is the same as It is assumed that model numbers in the Product table are unique for all the makers and product types
.
Upvotes: 1