How to calculate cumulative total of 365 days joining two different years?

Asked

Viewed 278 times

3

I’m trying to verify the accumulated total of a certain amount, which starts in September of a year and goes through August of the following year, and then starts again. I’m doing this with a long historical series, so it would be n sums for various data.

I’ve tried that sql:

SELECT ad.cod, ad.data, 
       SUM(ad.value)
            OVER(ORDER BY ad.data ROWS BETWEEN 364 PRECEDING AND CURRENT ROW) AS sum_value
FROM mytable ad 
  • You have two dates and want to sum up a column between those dates? This?

  • I have data from 2009 to 2015, sorted by day. I want to add up the total from September 2009 until August 2010, followed from September 2010 until August 2011, and so on

1 answer

0

select
    to_char(date_trunc('year', ad.data - interval '8 months'), 'YYYY'),
    sum(ad.value)
from mytable ad
group by 1

Browser other questions tagged

You are not signed in. Login or sign up in order to post.