CODEWITHSUNDEEP
mysqlsql
Colleen
Colleen

Reputation: 25499

sql select a moving range

I have a table with columns year and movie ids, and for each year, I want to get the count for of the ids with year within 10 years of that year.

e.g. if I had the data: year | id 1950 1 1951 2 1960 1

I would want to return year | count 1950 3 1951 3 1960 1

I thought I could do it like this

select m1.year, count(m1.id)
  from movie m1
  join movie m2
    on m1.id=m2.id
 where m2.year>=m1.year
   and m2.year<=m1.year+9
 group by m1.year
 order by m1.year;

but this just returns the movies in each year (or seems to, since the results are identical to select year, count(id) from movie group by year;)

what am I doing wrong?

Upvotes: 0

Views: 342

Answers (1)

Benoit
Benoit

Reputation: 79205

Joining on ids is not what you want: you'll get a 1<=>1 relationship most probably (May I assume id is a key?

SELECT m1.year, COUNT(*)
  FROM (SELECT DISTINCT year FROM movie) m1
 CROSS JOIN movie M2
 WHERE m2.year - m1.year BETWEEN 0 AND 9
 GROUP BY m1.year
 ORDER BY m1.year

Upvotes: 1

Related Questions