CODEWITHSUNDEEP
sqlpostgresql
AswinRajaram
AswinRajaram

Reputation: 1632

Customers who bought the exact 2 items

I have a table called order_details which contains 3 columns order_id, customer_id, and item_name

I need to find the customer_ids of those customers who bought both Kindle and Alexa

This is how my table looks like.

enter image description here

These are the DML and DDL commands for your reference:

create table personal.order_details(
order_id varchar(10),
customer_id varchar(10),
item_name varchar(10))

insert into personal.order_details values
('1000','C01','Alexa'),
('1000','C01','Kindle'),
('1001','C02','Alexa'),
('1002','C03','Alexa'),
('1002','C03','Kindle')

Thanks in advance.

Upvotes: 0

Views: 105

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

You can use aggregation and a having clause:

select od.customer_id
from personal.order_details od
where od.item_name in ('Alexa', 'Kindle')    -- rows that have either
group by od.customer_id
having count(distinct od.item_name) = 2;     -- customers that have both

Upvotes: 1

Related Questions