CODEWITHSUNDEEP
sqlsql-servert-sql
Ekim
Ekim

Reputation: 5

Add additional column to give a count on number of occurance on an existing column

I have an existing table with many fields of personal data. For each personal records, there is a unique reference number

I am trying to do a script that can add a new column onto the existing table This new added column is to hold number count on the number of time the unique reference on each row have came up.

For example:

---------------------------
UniqueID | PersonlData1 | PersonalData2 |
A        | A1           | A2            |
B        | B1           | B2            |
C        | C1           | C2            |
D        | D1           | D2            |
A        | AA1          | AA2           |
D        | DD1          | DD2           |

To become:

---------------------------
UniqueID | PersonlData1 | PersonalData2 | CountID |
A        | A1           | A2            | 2       |
B        | B1           | B2            | 1       |
C        | C1           | C2            | 1       |
D        | D1           | D2            | 2       |
A        | AA1          | AA2           | 2       |
D        | DD1          | DD2           | 2       |

Upvotes: 0

Views: 920

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

We can try using COUNT as an analytic function here:

SELECT
    UniqueID,
    Person1Data1,
    PersonalData2,
    COUNT(*) OVER (PARTITION BY UniqueID) CountID
FROM yourTable;

Upvotes: 2

Related Questions