CODEWITHSUNDEEP
mysqlsqlgroup-by
guyts
guyts

Reputation: 949

Group By to find differences between elements of table in SQL?

I have a table with items indices, scores and years. I would like to count each incident where a person's score went up between two years (table is sorted by date), so that for the following table:

person_index   score   year
3              76      2003
3              86      2004
3              86      2005
3              87      2006
4              55      2005
4              91      2006

I will get an output of person with index 3 with 2 changes, and person 4 with 1 change:

person_index   times_changed
3              2
4              1

I'm using MySQL 5.7

Upvotes: 0

Views: 101

Answers (2)

forpas
forpas

Reputation: 164064

Join the table by itself on the condition a person's score went up between two years:

select 
  t.person_index,  
  count(*) times_changed
from tablename t
inner join tablename tt
on t.person_index = tt.person_index
and tt.year - t.year = 2
and tt.score > t.score
group by t.person_index

See the demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You need the previous value. One method is a correlated subquery:

select person_id,
       sum( score > prev_score ) as times_changed
from (select t.*,
             (select t2.score
              from t t2
              where t2.person_index = t.person_index and
                    t2.year < t.year
              order by t2.year desc
              limit 1
             ) as prev_score
      from t
     ) t
group by person_id;

Note that this version will return persons with 0 changes.

Upvotes: 1

Related Questions