Cumulative sum where column match

Question

I want to make a query that gives me a cumulative sum for all weeks, but presents them in a crescent order like

Person | Week 1 | Week2 = Week1 + Week2 | Week 3 = Week 1 + 2 + 3|

My table is like

ID | Week | Person | Item 1 | Item 2 | Item 3 | Iteration

A week result is the sum of all items, and the iteration should be the highest number for the week.

So far I've come up to this, but the result is not what is expected, as the week1 sum is equal for all rows, which is wrong

SELECT person, w1sum, w2sum
FROM table AS t1, (

SELECT SUM( item1 + item2 + item3 ) AS w1sum
FROM table 
WHERE week =1
AND iteration
IN (

SELECT MAX( iteration ) 
FROM table
)
    GROUP BY person

) AS week1, 
(

SELECT SUM( item1 + item2 + item3 ) AS w2sum
FROM table 
WHERE week = 1 AND week = 2
AND iteration
IN (

SELECT MAX( iteration ) 
FROM table
)
    GROUP BY person

) AS week2

GROUP BY person

Answer 1

I think you can do what you want using conditional aggregation:

select person,
       sum(case when week in (1) then item1 + item2 + item3 else 0 end) as week1,
       sum(case when week in (1, 2) then item1 + item2 + item3 else 0 end) as week2
       sum(case when week in (1, 2, 3) then item1 + item2 + item3 else 0 end) as week3
from table t
where not exists (select 1
                  from table t2
                  where t2.person = t.person and
                        t2.week = t.week and
                        t2.iteration > t.iteration
                 )
group by person;

The where clause gets the last iteration for each person for each week.