unexpected output with group by

Here is the DDL and DML:

create table fb_customers (
customer_id int,
customer_name varchar,
product_bought varchar);

insert into fb_customers values
(1, 'james', 'A'),(2, 'james', 'B'), (3, 'james', 'A'), (4, 'james', 'C'), (5, 'ada', 'A'), (6, 'ada', 'A'), (7, 'Tom', 'B'), (8, 'Leo', 'C');

when I select customer_name and product_bought with group by:

select customer_name, product_bought from fb_customers
group by customer_name, product_bought;

the result is surprisingly not grouped by name automatically – I thought with group by the result should be grouped by customer_name and product_bought but we can see james is separated and scattered instead of grouped together.

Now I’d like to find the customers who bought both A and B, and here is my query:

select customer_name from fb_customers
group by customer_name, product_bought
having sum(case when product_bought = 'A' then 1
                when product_bought = 'B' then 1
                else 0
           end) = 2;

and the result is surprising include ada who should not be there:

where am I wrong with these two questions? Thanks

Answer

You can try the below – DEMO Here

select customer_name from fb_customers
where product_bought in ('A','B')
group by customer_name
having count(distinct product_bought)=2

Advertisement

Answer