I have a following table:

From here I need to have calculated sum of all values for every given row/date that are within dates in Start and End Date columns (boundaries included), so it is grouped by item and per date.

Thanks for your help!

CodePudding user response：

A simple way is to use a correlated subquery to do the calculation:

select item, date,
       (select sum(value) from table t2
        where t2.item = t1.item
          and t2.date between t1.start_date and t1.end_date)
from table t1

CodePudding user response：

Assuming you are only working with one month of data - one option could be to extract the day value from each date and then sum up each value while grouping by day.

This can be done by firstly creating a new variable in your dataset to store the day value:

alter table dataset add day int;

Values can then be extracted:

update dataset set day=extract(day from date);

Then, it is a matter of grouping the values by day:

select day, sum(value) as value_sum from dataset group by day order by day;