Level 102
Could you perhaps explain what you are looking for? Duplicates of what?
Sep 29, 2022
8
Level 1
SQL find Duplicate Query
I am trying to find duplicates in my SQL table with this query
SELECT a.*
FROM transactions a
JOIN (SELECT user_id, details, COUNT(*)
FROM transactions
GROUP BY user_id, details
HAVING count(*) > 1 ) b
ON a.user_id= b.user_id
AND a.details= b.details
ORDER BY a.user_id
The problem is I am trying to narrow it down with a WHERE condition and I just could not get my way around it
Level 1
@Sinnbeck duplicate rows in SQL Database
Level 102
@Agbaje but on what columns. I can see it's the the transactions table. user_id and details columns?
Level 1
@Sinnbeck Yes But I want to add WHERE remark ="Paypal" And this is not working
Level 102
@Agbaje ok let me give it a shot. What kind of data is in details? I assume just want to know the user_id + details that's is duplicated so you can find them afterwards
Level 1
@Sinnbeck It is text data. it just gives more details about the transactions e.g You purchased an Item using Paypal.
Level 102
@Agbaje Ok how about something simple like this
select count(*) as total, CONCAT(user_id, '-', details)
from transactions
group by CONCAT(user_id, details)
having total > 1
order by total DESC
Should give you an easy to read list :)
Level 102
Or you can do
select count(*) as total, user_id, details
from transactions
group by user_id, details
having total > 1
order by user_id, total DESC
Please or to participate in this conversation.