SQL query help

1

Hello SQL Developers,

I'm new to SQL and need help producing an output for the following:

Get the makers who produce only one product type and more than one model.
Output: maker, type.

The makers values are A,B, C, D and the values for types are PC, Laptop, Printer.

I have been able to get all makers producing more than one model with the below query:

Select maker from Product

Group by maker Having
count(model) > 1

Next step, I'm trying to get all makers producing only one type which I'm not sure and don't know how to combine these two.

Can anyone please help?

Thanks,
H

2

You'll need a WHERE clause. Since this looks like homework, give it a go and post your modified query if you still are stuck

https://www.tutorialspoint.com/sql/sql-where-clause.htm

3

I, too, think this is homework, so I can't directly give you the answer.

But I can tell you, you're very close already! You've already done the hard part, using HAVING. You just need to add "AND" your new condition to the HAVING [In my view, you need HAVING here, not WHERE]:

SELECT maker 
FROM Product
GROUP BY maker
HAVING COUNT(model) > 1 AND ?add_something_here?

Final hint: Look at all the options you have when using COUNT.

4

Hmmm. does that mean one particular type (how I read it) or just one type (how I think Scott read it)

5

Thank you, Developers!

I think I'm still stuck!

SELECT maker, type
FROM Product
GROUP BY maker
HAVING COUNT(model) > 1 and count(distinct type) = 1

@gbritton - The type refers to just one type - which in this case is a Laptop or PC or Printer.

If it was just the maker, I'm getting it right. But determining the type is where I'm stuck with the query. All your help is appreciated.

Thanks,
H

6

Nicely done! Just one final "trick" to select the type. Since we know there's only one type anyway, it doesn't matter whether you list min or max, take your pick.

SELECT maker, min(type) as type
FROM Product
GROUP BY maker
HAVING COUNT(model) > 1 and count(distinct type) = 1

7

Thank you very much! I tried this one and got it working.

select distinct p1.maker, p1.type from Product p1
inner join
(SELECT p1.maker
FROM Product p1
GROUP BY p1.maker
HAVING COUNT(p1.model) > 1 and count(distinct p1.type) = 1)p3
on p1.maker = p3.maker