CODEWITHSUNDEEP
sqlselectaggregate-functions
ha9u63a7
ha9u63a7

Reputation: 6872

An Efficient Summarisation of this SQL Query Solution

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

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

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

Related Questions