I have a table like this
| col1 | col2 |
|---|---|
| First | row |
| Second | a |
| First | b |
| Second | row |
| First | c |
| Second | row |
The output required is like below:
| col1 | col2 | col3 |
|---|---|---|
| First | row | 1 |
| Second | a | 1 |
| First | b | 1 |
| Second | row | 2 |
| First | c | 2 |
| Second | row | 3 |
The logic is , whenever we are getting the value “row” in col2, the col3 will increment the counter, otherwise will skip it. Please help.
Advertisement
Answer
You need a column that specifies ordering because SQL tables represent unordered sets.
With such a column, just use conditional aggregation:
select t.*,
sum(case when col2 = 'myval' then 1 else 0 end) over (order by <ordering col>) as col3
from t;