| ID | value |
|---|---|
| 1 | 4 |
| 1 | 5 |
| 3 | 4 |
| 2 | 10 |
I want to add another column called count, that has for each id the number of observations.
Transformed table
| id | value | count |
|---|---|---|
| 1 | 4 | 2 |
| 1 | 5 | 2 |
| 3 | 4 | 1 |
| 2 | 10 | 1 |
CodePudding user response：
You can use the OVER() clause to aggregate.
SELECT
ID,
value,
[count] = COUNT(*) OVER (PARTITION BY ID)
FROM dbo.TableName;