SQL: how to group years together

Question

 'SELECT year, scores FROM database'

If my data looks something like

 year     score
 1901      100 
 1902      300
 1902      300
 1911      300
 1921      100
 .         .
 .         .
 .         .
 2000      

And I'm interested in looking at the years in groups of 10 and summing up their respective scores. So for years 1901 to 1910, the total score would be 700. For 1911 - 1920, it would be 300. How can I perform this in SQL?

Answer 1

Depending on your actual DBMS something like this:

select cast( (year / 10) as integer) * 10 as year_group,
       sum(score)
from scores
group by year_group
order by year_group;

The above is ANSI SQL and will work on many DBMS. The cast() operator however is not supported by all DBMS in the same way, so the syntax for your DBMS might be slightly different.

SQLFiddle: http://sqlfiddle.com/#!15/7e4fb/1