how to count specific product in sql table

Posted on

Question :

I have a table product and its fields are maker, type and model I want to get the makers who produce only one product type and more than one model. Here is something I am trying

select count(distinct type) as type from product where type = '1'

expe data is: maker = D & type = printer

Answer :

Sample data:

create table product (maker varchar2(100), type char(1), model varchar2(100));
insert into product values ('ABC', '1', 'MODEL1');
insert into product values ('ABC', '1', 'MODEL2');
insert into product values ('ABC', '2', 'MODEL3');
insert into product values ('DEF', '3', 'MODEL4');
insert into product values ('GHI', '4', 'MODEL5');
insert into product values ('GHI', '4', 'MODEL6');
commit;

Query:

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

MAKER
-----
GHI

First we GROUP BY maker, so we inspect each maker on its own. In the HAVING part, count(distinct type) = 1 means the maker produces exactly 1 type of product. Finally, count(distinct model) > 1 means the maker produces more than one model.

With count(distinct type) = 1, we have only 1 distinct type value, so we got lucky in this case and we can use a simple aggregate function on type, we will get the expected value:

select
  maker,
  min(type)
from
  product
group by
  maker
having
  count(distinct type) = 1 
  and count(distinct model) > 1
;

MAKER TYPE
----- ----
GHI   4

Leave a Reply

Your email address will not be published. Required fields are marked *