count continuously postgresql data

Question

i need help with counting some data

this what i want

| user_id | action_id | count       |
------------------------------------- 
| 1       |    1      | 1           |
| 2       |    2      | 1           |
| 3       |    2      | 2           |
| 4       |    3      | 1           |
| 5       |    3      | 2           |
| 6       |    3      | 3           |
| 7       |    4      | 1           |
| 8       |    5      | 1           |
| 9       |    5      | 2           |
| 10      |    6      | 1           |

this is what i have

| user_id | action_id | count |
------------------------------- 
| 1       |    1      | 1     |
| 2       |    2      | 1     |
| 3       |    2      | 1     |
| 4       |    3      | 1     |
| 5       |    3      | 1     |
| 6       |    3      | 1     |
| 7       |    4      | 1     |
| 8       |    5      | 1     |
| 9       |    5      | 1     |
| 10      |    6      | 1     |

i really need it for create some research about second action from users how do i do it?

thank you

Tim Biegeleisen · Accepted Answer · 2018-10-31 09:28:00Z

1

Using ROW_NUMBER should work here:

SELECT
    user_id,
    action_id,
    ROW_NUMBER() OVER (PARTITION BY action_id ORDER BY user_id) count
FROM yourTable
ORDER BY
    user_id;

Demo

answered Oct 31, 2018 at 9:28

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Biegeleisen Over a year ago

@AzizZulHazmi That's a different question. I suggest asking another question.

Collectives™ on Stack Overflow

count continuously postgresql data

1 Answer 1

Demo

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related