I have a binary flag ('Y' or 'N') column, from a table, like below:
| ID | prev_appt_flag |
|---|---|
| 1 | N |
| 2 | Y |
| 3 | Y |
| 4 | N |
| 5 | N |
| 6 | Y |
| 7 | N |
| 8 | Y |
| 9 | Y |
| 10 | N |
| 11 | Y |
Then, I want a row counter that will only add to its count, whenever it finds 'N'. So for this example, the expected outcome would be:
| ID | prev_appt_flag | final_goal |
|---|---|---|
| 1 | N | 1 |
| 2 | Y | 1 |
| 3 | Y | 1 |
| 4 | N | 2 |
| 5 | N | 3 |
| 6 | Y | 3 |
| 7 | N | 4 |
| 8 | Y | 4 |
| 9 | Y | 4 |
| 10 | N | 5 |
| 11 | Y | 5 |
CodePudding user response：
We can use COUNT() here as an analytic function:
SELECT ID, prev_appt_flag,
COUNT(CASE WHEN prev_appt_flag = 'N' THEN 1 END) OVER (ORDER BY ID) AS final_goal
FROM yourTable
ORDER BY ID;