Selecting all data where id have duplicates-CodePudding

i was trying to take all data from database where the customers shops more than 1

current code:

select * from sales s 
join closest_dealerships cd 
on s.customer_id=cd.customer_id 
order by s.customer_id;

what ive tried:

select *,
    case
        when s.customer_id in (select s.customer_id from sales s 
                            group by (s.customer_id)
                            having count(s.customer_id) >1
                            order by s.customer_id)
    end 
from sales s 
join closest_dealerships cd 
on s.customer_id=cd.customer_id 
order by s.customer_id;

current output:

| customer_id | product_id | column1 | columnx
|      1      |      8     |         |
|      2      |      7     |         |
|      2      |      1     |         |
|      3      |     12     |         |
|      4      |     23     |         |

output i was hoping:

| customer_id | product_id | column1 | columnx
|      2      |      7     |         |
|      2      |      1     |         |
|      4      |     23     |         |
|      4      |      9     |         |
|      4      |     12     |         |

CodePudding user response：

You want to show the joined rows, but only if there is more than one row for the customer. So, count the rows per customer and only keep data where that count is greater than one.

select *
from
(
  select s.*, c.*, count(*) over (partition by s.customer_id) as cnt
  from sales s 
  join closest_dealerships cd using (customer_id)
) counted
where cnt > 1
order by customer_id;