SQL: Select two columns by single column in group by with only having condition

Question

I'm a bit new to SQL; I have a single table Products:

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

I need to select maker an type by maker who has more than 1 model but all of those model are of a single type. So the output should be

maker  type
D     Printer

When I perform grouping only by maker everything is ok, but when use both maker and type = everything goes wrong :( Is that possible to perform group by a single column but have 2 in output? The correct response without a type returns next query:

Select maker from product
group by maker
having count(model)>1
and count(distinct type)=1

But when I select maker,type - it goes wrong :( also wrong when use select maker,type and group by maker,type

Spent about 4 hours to solve that, will very appreciate your help Thanks in advance!

Answer 1

To answer the question, no you cannot include a column in the select that wasn't in the grouping unless it has some sort of analytic. You have to use the analytic to tell Oracle how to handle multiple rows.

Given that you are guaranteed to have just one type, a simple max would be safe.

SELECT maker, MAX(TYPE) AS type
  FROM product
 GROUP BY maker
HAVING COUNT(MODEL) > 1 AND COUNT(DISTINCT TYPE) = 1

Answer 2

You're looking for this:

SELECT MAKER, TYPE, COUNT(*) AS NUMBER_OF_MODELS
FROM PRODUCT
GROUP BY MAKER, TYPE
HAVING COUNT(*) > 1