Selecting all data where id have duplicates

Question

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     |         |

Your sample input and output data do not match up. Please fix your data. — Tim Biegeleisen, Nov 11 '22 at 12:25
The top answer here should help you figure it out https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table — Ineffable21, Nov 11 '22 at 13:01
You need to specify what sql exactly you are using. Tagging Sql and dbeaver could mean that you are using sql server, mysql, postgre and so on. — Ineffable21, Nov 11 '22 at 13:09
You don't need a case here. You can just group by customer_id and select where count(customer_id) > 1. This also reveals a flaw to you. If you have duplicates, what should be the value of the other columns? Say you have 2 duplicate customer_ids, but columx is different for each. — The Fool, Nov 11 '22 at 14:12

score 1 · Accepted Answer · answered Nov 11 '22 at 14:57

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;

Selecting all data where id have duplicates

1 Answers1