Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create table account (
  size int,
  id int,
  name text
);

insert into account values
(100, 1, 'John'), (200, 2, 'Mary'), (300, 3, 'Jane'), 
(400, 4, 'Anne'), (100, 5, 'Mike'), (600, 6, 'Joanne');
6 rows affected

with recursive rns as ( -- Assign row numbers as rn in case of gaps in id
  select *, row_number() over (order by id) as rn
    from account
), sumgrp as ( -- Start with first row
  select size, id, name, rn, 1 as grp, size as running_total
    from rns
   where rn = 1
  union all
  select n.size, n.id, n.name, n.rn, 
         case  -- Increment the grp when running_total exceeds 600
           when p.running_total + n.size > 600 then p.grp + 1 
           else p.grp 
         end as grp,
         case  -- Reset the running_total when it exceeds 600
           when p.running_total + n.size > 600 then n.size 
           else p.running_total + n.size 
         end as running_total
    from sumgrp p
         join rns n on n.rn = p.rn + 1
)
select grp, size, id, name, running_total
  from sumgrp 
 order by id;


grp
size
id
name
running_total


1
100
1
John
100

1
200
2
Mary
300

1
300
3
Jane
600

2
400
4
Anne
400

2
100
5
Mike
500

3
600
6
Joanne
600

grp	size	id	name	running_total
1	100	1	John	100
1	200	2	Mary	300
1	300	3	Jane	600
2	400	4	Anne	400
2	100	5	Mike	500
3	600	6	Joanne	600