General
Enthusiast

## CSUM(1), But Increment (NOT RESET) Based on Some Condition

Hello Everyone! I'm stuck and have seen similar answers but nothing that quite fits the bill. Given the following table:

USER_ID  | STATUS

12300      | YES

12300      | YES

15000      | NO

15000      | NO

15005      | NO

15005      | YES

15005      | NO

I'd like to begin at the number 1, and then only increment based on some condition (STATUS = 'YES'). The desired output looks like:

USER_ID  | STATUS | SEQUENCE

12300      | YES       |    1

12300      | YES       |    2

15000      | NO        |    2

15000      | NO        |    2

15005      | NO        |    2

15005      | YES       |    3

15005      | NO        |    3

Essentially, whenever you hit a 'YES' then increment; otherwise, just maintain the current tally. I provided USER_ID col but really that's irrelevant here.

What am I missing? I can't figure this out.

4 REPLIES
Senior Apprentice

## Re: CSUM(1), But Increment (NOT RESET) Based on Some Condition

Assuming there's a column to order accordingly:

`sum(case when status = 'YES' then 1 else 0 end)over (order by whatever)`

Btw, CSUM is deprecated syntax, there's ROW_NUMBER, etc. instead.

Enthusiast

## Re: CSUM(1), But Increment (NOT RESET) Based on Some Condition

Hi Dieter - First of all, thank you. I've been reading over a lot of other posts the past week (as I've been learning Teradata SQL) and your answers are awesome.

Second, this doesn't quite solve the problem for me. It's just putting the total in every row. e.g.:

SELECT count(*)

FROM sessions

WHERE status = 'YES'

is just being put into every row.

I need to start at the number 1, propogate "1" until I hit a yes, then increment to 2, propogate "2" until I hit a yes, then increment to 3... and so forth.

Am I not intrepreting your instructions as you intended?

Senior Apprentice

## Re: CSUM(1), But Increment (NOT RESET) Based on Some Condition

Oops, I forgot ROWS, sorry :)

`sum(case when status = 'YES' then 1 else 0 end)over (order by whatever      rows unbounded preceding)`
Enthusiast

## Re: CSUM(1), But Increment (NOT RESET) Based on Some Condition

DIETER YOU ARE THE MAN!

Thanks, you really helped me out :)