Search code examples
sqlcountcreate-table

SQL field default "count(another_field) +1"

I need to create a field COUNT whose default value is the automatically generated count of times NAME has appeared in that table till now, as shown in example below. Since i am adding the field to an existing table, i also need to populate existing rows. How best to go about this please?

ID  NAME    COUNT
1   peter   1
2   jane    1
3   peter   2
4   peter   3
5   frank   1
6   jane    2
7   peter   4
Solution

  • You would do this when you are querying the table, using the ANSI-standard row-number function:

    select id, name, row_number() over (partition by name order by id) as seqnum
from t;