CODEWITHSUNDEEP
sqlsqlite
dousin
dousin

Reputation: 526

Counting value changes in a table column

Consider a sorted table (according to id). How to count number of changes of the values in column 'value'? In the following example, the number of changes is 3 (10 to 20, 20 to 10, 10 to 30). Thx

id    value
 1      10
 2      10
 3      20
 4      20
 5      10
 6      30
 7      30

Upvotes: 5

Views: 12040

Answers (3)

Silvia Parfeni
Silvia Parfeni

Reputation: 528

I like to use this function: lead() over(). If you have, for example, this select:

select id, value
from youTable
where id <= 7

I find next value of "value" column with lead() function and I compare with the current one so :

select count(1) as number from
   (select lead(value) over(order by id) as nextValue, id, value
    from youTable
    where id <= 7) as tmp
where tmp.nextValue <> tmp.value

now, in number I have the numbers of changed value into "value" column

Upvotes: 2

Charles Bretana
Charles Bretana

Reputation: 146409

if ids are sequential with no gaps...

Select count(*)
From table t1
   join table t2 
       on t2.id = t1.id + 1
where t2.value <> t1.value

else... 

Select count(*)
From table t1
   join table t2 
       on t2.id = (Select min(id)
                   From table 
                   where id > t1.id)
where t2.value <> t1.value

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can identify the changes by using a correlated subquery. Then add them up. A change occurs when the value is different from the previous value:

select count(*)
from (select t.*,
             (select value
              from table t2
              where t2.id < t.id
              order by t2.id desc
              limit 1
             ) as prev_value
      from table t
     ) t
where prev_value <> value;

Note that due to prev_value being NULL for the first row, this ignores that one.

If you can guarantee that the ids are sequential with no gaps, you can do this more efficiently with a join:

select count(*)
from table t join
     table tprev
     on t.id = tprev.id + 1
where t.value <> tprev.value;

Upvotes: 2

Related Questions