CODEWITHSUNDEEP
sqlsql-server
JDHatman
JDHatman

Reputation: 23

SQL Show total number duplicate records on each row

I would like to add a column that shows how many other times 'name' appears in other rows. Basically a total number of times that name appears, but listed on each row.

select number, name from table;

Sample data before addition:

number  name     
1234    storeA
1235    storeA
1236    storeA
1237    storeB
1238    storeC
1239    storeC

Desired output after addition

number  name    Total
1234    storeA  3
1235    storeA  3
1236    storeA  3
1237    storeB  1
1238    storeC  2
1239    storeC  2

Upvotes: 0

Views: 51

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use window function :

select t.*,
       count(*) over (partition by name) as total
from table t;

However, ANSI SQL standard offers correlates subquery approach if window function won't work : 

select t.*,
       (select count(*)
        from table t1
        where t1.name = t.name
       ) as total
from table t;

Upvotes: 5

Related Questions