CODEWITHSUNDEEP
sqldatabasems-access-2007
user3656350
user3656350

Reputation: 1

How to get count of duplicate values in another field in sql?

I have table like

id  area    Count1
39  AB      
40  AB  
41  AB
42  AB  
82  Ag  
83  Ag  
98  Ai  
100 Ai  
183 Am

I need the count of duplicate values in another field say 'count1' since id is important

I need the answer to be like 

id  area    Count1
39  AB      1   
40  AB      2
41  AB      3
42  AB      4
82  Ag      1
83  Ag      2
98  Ai      1
100 Ai      2
183 Am      1

I am getting the count of duplicate values Presently I am using ms access 2007

Thank you

Upvotes: 0

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can do this with a correlated subquery:

select id, area,
       (select count(*)
        from table as t2
        where t2.area = t.area and
              t2.id <= t.id
       ) as Count1
from table as t;

Upvotes: 2

Related Questions