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

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 :)