I have the following PostgreSQL 13 table:
| id | date |
|---|---|
| 1 | 2021-01-01 |
| 1 | 2021-01-02 |
| 1 | 2021-01-03 |
| 2 | 2021-01-01 |
| 2 | 2021-01-02 |
| 3 | 2021-01-03 |
| 4 | 2021-01-03 |
The table is sorted by id and date as shown. I would like to incrementally count each instance of id. Like this:
| id | date | count |
|---|---|---|
| 1 | 2021-01-01 | 1 |
| 1 | 2021-01-02 | 2 |
| 1 | 2021-01-03 | 3 |
| 2 | 2021-01-01 | 1 |
| 2 | 2021-01-02 | 2 |
| 3 | 2021-01-03 | 1 |
| 4 | 2021-01-03 | 1 |
Is this possible using just SQL? Some of the answers I have seen involve using the group by clause, like this one:
SELECT id, count(*) FROM table GROUP BY id
Afterwards, this result is joined with the table to get a count for each id. This is not what I am looking for.
Using CTE's is ok. I would like to avoid using stored procedures if possible. Thanks.
