Post reply

Can you create a cumulative count field within groups?

  • October 21, 2004 at 6:56 am

    #60467

    Dear Forum,

    I have a long table of data (see example below) with multiple records for each person and the data in order of month. I would like to create a cumulative couting field that counts within each person like the variable called Count.

    However, I'm really struggling to create this seemingly simple count field using TSQL. Can it be done?

    Thanks, Bill

    ID               Month           Count

    1                    1                1

    1                    2                2

    1                    5                3

    2                    2                1

    2                    4                2

    2                    6                3

    2                    7                4 

    3                    1                1

    3                    4                2

    3                    8                3

  • October 21, 2004 at 9:54 am

    #527490

    SELECT A.ID, A.Month, ( SELECT COUNT(*) FROM MyTable B WHERE B.ID = A.ID and B.Month <= A.Month ) AS Count FROM MyTable A

    The above gets messed up a touch when a given Month can be used more than once for a given ID. Basically, both instances would have the same count = the inclusion of both instances plus prior months. If you need to distinguish those counds you will need a finer grained key involved.

  • October 22, 2004 at 3:31 am

    #527612

    Aaron,

    Many thaks this solution it works great .

    I think you've provided a correlated sub-query which I should learn more about as they seem very powerful.

    Cheers, Bill

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply