sum by increment postgres

Question

how to calculate the sum of points each week?

Table: essai

Here is the table and the last column (sum) is the resulsutat I want to get.

Answer 1

You can use the window variant of sum:

SELECT *,
       SUM(opints) OVER (ORDER BY number_week ASC
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM   essai

Answer 2

if:

db=# create table m (w int, n text, p int);
CREATE TABLE
db=# insert into m values (4,'a',2),(3,'a',6),(2,'a',1),(1,'a',3);
INSERT 0 4

then:

db=# with c as (select *,sum(p) over (partition by n order by w) from m)
select * from c order by w desc;
 w | n | p | sum
---+---+---+-----
 4 | a | 2 |  12
 3 | a | 6 |  10
 2 | a | 1 |   4
 1 | a | 3 |   3
(4 rows)