Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

SQL: How to count number of duplicate entries and add that number into same row

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

Example of outcome

I’ve tried both solutions described in a post here
How to count the number of duplicate in sql

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading