I have a table with numerous duplicate records that I can identify by ID. I want to count number of entries inside of the table for each row (for each ID) and add that number inside of specific column (Num of Duplicates).
Example of table:
ID
AB1
AB2
AB1
AB3
AB2
AB1
I’ve tried both solutions described in a post here
How to count the number of duplicate in sql
First one filled Num of Duplicates column with the same number of total count of entries inside of the table. i.e:
ID Num of DUP
AB1 6
AB2 6
AB1 6
AB3 6
AB2 6
AB1 6
Solution 2 – no results at all.
>Solution :
We can use COUNT() here as an analytic function:
SELECT ID, COUNT(*) OVER (PARTITION BY ID) AS cnt
FROM yourTable;