Counter in a Sql statement

Hi,

Given a column payment_type, where payment type varies, I need to count each time when the value of the payment_type changes and display the total count in the end.

For example,
payment_typ is 1 1 2 2 2 2 2 3 3 3 3 3 4. Total count should be 3.

Summary:I need to traverse the values, capture the change and increment the counter.

So far, I have this:

with data_set_1(payment_type) as
(
values 1,2,2,3,3,4,4
)
select sum(case)
from
(select payment_type, case payment_type
when LAG(payment_type then 0
else 1
end case
from data_set_1)as counter
I have a problem with LAG function (which gets the previous value of payment_type). It works with Oracle but not with DB2. Is thier any function equivalent to LAG in DB2? Or may there is another way to get the previous value. What am I missing here?

Any help is appreciated.

Thanks!

Hi, Thanks for the response but I forgot to mention that payment_type can randomly re-occur multiple times.

example: 1,2,2,3,3,4,4,4,5,2,3

You solution will report a count of 4 where as the payment_type has changed 6 times.

Correct answer should be 6

Your requirement seems a bit flaky, some RDBMS may NOT return the rows in the same order they were inserted, therefore the “count” of changes may vary each time you execute the query and/or between different RDBMS.