Count only when the next row is different from the previous row

Question

I have a table with 12 registers. I want to count the row just if the column "AREA_OPERATIVA" is different from the previous row (ordering by date asc).

For example, from row 1 to row 2 it shouldn't count anything because both have same area 'CROSS' but between rows 2 and 3, it should count (or sum 1, I don't care) since 'CROSS' and 'UTRDANIOS' are different. So the final count should be 3 for the whole table.

Is it possible to do this via query or do I need to make a script with a cursor for this purpose?

I've tried this:

SELECT  a.creclama, 
sum (CASE WHEN b.area_operativa  NOT LIKE  a.area_operativa THEN 1 ELSE 0 END) AS increment
FROM TR_ASGAREOPE a
INNER JOIN TR_ASGAREOPE b ON a.creclama = b.creclama 
                          and a.cdistribuidora = b.cdistribuidora 
                          and a.secuencia = b.secuencia
WHERE a.creclama = 10008354
group by a.creclama;

But is counting the full 12 rows.

EDIT:

Finally I could resolve this by the next query:

select sum (
    CASE WHEN (comparacion.area_operativa  not like  comparacion.siguiente_fila THEN 1 ELSE 0 END) AS incremento 
from (    
    select creclama,
           area_operativa, 
           lead(area_operativa) over (order by fmodifica) as siguiente_fila
    from TR_ASGAREOPE
    where creclama = 10008354
    order by fmodifica
);

Hope it is useful for someone in the future, it really got me stuck for a day. Thank you all guys.

Answer 1

You could try using analytic functions like lead or lag, for example

    SELECT CRECLAMA,
           CASE WHEN AREA_OPERATIVA <> NEXTROW THEN 1 ELSE 0 END AS INCREMENT
      FROM (
            SELECT CRECLAMA,
                   AREA_OPERATIVA,
                   LEAD(AREA_OPERATIVA) OVER (PARTITION BY 1 ORDER BY CRECLAMA) AS NEXTROW
              FROM TR_ASGAREOPE
            )