CODEWITHSUNDEEP
sqlcountgroup-by
Frankie Drake
Frankie Drake

Reputation: 1388

trouble using count() and group by

I have table named Products, that looks like:

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

And I need to get the maker that produces more than 1 model but that models should be the same type... Here it should be maker = D and Type = Printer.

I spent the whole day using count(model)>1 and count(type)=1 etc. Nothing works.

Upvotes: 0

Views: 46

Answers (4)

Ramesh
Ramesh

Reputation: 28

With TempTable
AS(
    SELECT
        Maker ,Count(DISTINCT type) Count1,Count(model) as mcount
    FROM
        Products 
    GROUP BY
        Maker
    HAVING Count(DISTINCT type)=1
)

Select a.Maker from TempTable a
Where a.mcount IN (Select MAX(mcount) from TempTable)

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32392

select distinct maker, type
from products t1
where exists (
    select 1 from products t2
    where t1.maker = t2.maker
    and t1.model <> t2.model
    and t1.type = t2.type
) and not exists (
    select 1 from products t2
    where t2.maker = t1.maker
    and t2.type <> t1.type
)

Upvotes: 1

Taryn
Taryn

Reputation: 247690

If you want to determine the maker that has more than one model of the same type, then you can use GROUP BY and HAVING to get the result:

select maker
from products
group by maker
having count(distinct model) > 1 -- more than one model
  and count(distinct type) = 1   -- same type

See SQL Fiddle with Demo If you want to return everything for each maker, then use can use

select p.maker, p.model, p.type
from products p
where maker in (select maker
                from products t
                group by maker
                having count(distinct model) > 1
                  and count(distinct type) = 1);

See Demo

Upvotes: 2

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

SELECT maker,
       MIN(type) AS type
FROM Products
GROUP BY maker
HAVING COUNT(DISTINCT type) = 1
       AND COUNT(DISTINCT model) >1

Upvotes: 2

Related Questions