sql - Group by every year, year+9 and get total sum of count

Question

How to group by specific range for every distinct years(by default years are distinct)?

Input:

my_table:

year  count
1961  2
1963  6
1969  3
1975  5
1976  9
1978  5
1983  3
1984  1
1985  3
1986  5
1987  6
1989  13
.
.
.

I have tried,

select sum(count) as total_count, 
       (year || '-' || year+9) as year_range 
from my_table 
group by year

But, the code is not giving correct results.

---

Expected output:

total_count  year_range
11           1961-1970
 9           1963-1972
22           1969-1978
23           1975-1984
23           1978-1987
.
.
.

Answer 1

You can do it with a self join and aggregation:

select sum(m2.count) total_count,  
       m1.year || '-' || (m1.year + 9) year_range
from my_table m1 left join my_table m2
on m2.year between m1.year and m1.year + 9
group by m1.year

See the demo.
Results:

> total_count | year_range
> ----------: | :---------
>          11 | 1961-1970 
>           9 | 1963-1972 
>          22 | 1969-1978 
>          23 | 1975-1984 
>          21 | 1976-1985 
>          23 | 1978-1987 
>          31 | 1983-1992 
>          28 | 1984-1993 
>          27 | 1985-1994 
>          24 | 1986-1995 
>          19 | 1987-1996 
>          13 | 1989-1998