CODEWITHSUNDEEP
mysqlsql
liontass
liontass

Reputation: 740

Count the subtract in a column between values

I have a table with a column with numerical values. I want to measure how many times the newest price differs from the previous one by 8 points eg...

enter image description here

First I subtract 1 from  2    166-158=8    I  want this to count             
Then  I subtract 2 from  3    158-143=15   I dont want this to count           
Then  I subtract 3 from 4     143-140=3    Neither this     
Then  I subtract 4 from 5     140-132=8    I want his to count

So the sql code must return 2 counts

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Use lag():

select count(*)
from (select t.*, lag(price) over (order by id) as prev_price
      from t
     ) t
where price = prev_price - 8;

Upvotes: 1

Related Questions