Michael, this is working perfectly! I learned a lot with your code. Thanks a lot for your help.
gert 2013/3/2 Michael Black <mdblac...@yahoo.com> > I think your "K" row was a typo on the CumulativeValue? > > CREATE TABLE [Test] (TextField, ValueField, CumulativeValue, BreakPoint > DEFAULT 10, GroupName); > CREATE TABLE [MyGroup](GroupName); > insert into [MyGroup] values(1); > create trigger trig1 before insert on [Test] > when 10 <= (select sum(ValueField) from [Test] where GroupName=(select > GroupName from [MyGroup])) > begin > update [MyGroup] set GroupName = (select GroupName+1 from > [MyGroup]); > end; > create trigger trig2 after insert on [Test] > begin > update [Test] set GroupName = (select GroupName from [MyGroup]) > where > rowid=new.rowid; > update [Test] set CumulativeValue = (select sum(ValueField) from > [Test] where GroupName=(select GroupName from [MyGroup])) where > rowid=new.rowid; > end; > insert into [Test] values('A', '2', null, '10',(select GroupName from > MyGroup)); > insert into [Test] values('B', '3', null, '10',(select GroupName from > MyGroup)); > insert into [Test] values('C', '2', null, '10',(select GroupName from > MyGroup)); > insert into [Test] values('D', '4', null, '10',(select GroupName from > MyGroup)); > insert into [Test] values('E', '5', null, '10',(select GroupName from > MyGroup)); > insert into [Test] values('F', '1', null, '10',(select GroupName from > MyGroup)); > insert into [Test] values('G', '1', null, '10',(select GroupName from > MyGroup)); > insert into [Test] values('H', '5', null, '10',(select GroupName from > MyGroup)); > insert into [Test] values('I', '11', null, '10',(select GroupName from > MyGroup)); > insert into [Test] values('J', '8', null, '10',(select GroupName from > MyGroup)); > insert into [Test] values('K', '2', null, '10',(select GroupName from > MyGroup)); > select * from [Test]; > A|2|2|10|1 > B|3|5|10|1 > C|2|7|10|1 > D|4|11|10|1 > E|5|5|10|2 > F|1|6|10|2 > G|1|7|10|2 > H|5|12|10|2 > I|11|11|10|3 > J|8|8|10|4 > K|2|10|10|4 > > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Gert Van Assche > Sent: Saturday, March 02, 2013 7:47 AM > To: sqlite-users > Subject: [sqlite] Break on cumulative sum > > All, I don't know how to achieve this: I need to put the cumulative sum in > a field, and create a group as soon as that cumulative sum is over a > breakpoint value (10). > > This is an example table: > > CREATE TABLE [Test] (TextField, ValueField, CumulativeValue, BreakPoint > DEFAULT 10, GroupName); > insert into [Test] values('A', '2', null, '10'); > insert into [Test] values('B', '3', null, '10'); > insert into [Test] values('C', '2', null, '10'); > insert into [Test] values('D', '4', null, '10'); > insert into [Test] values('E', '5', null, '10'); > insert into [Test] values('F', '1', null, '10'); > insert into [Test] values('G', '1', null, '10'); > insert into [Test] values('H', '5', null, '10'); > insert into [Test] values('I', '11', null, '10'); > insert into [Test] values('J', '8', null, '10'); > insert into [Test] values('K', '2', null, '10'); > > I'd like to end up with a table that looks like this: > > TextField ValueField CumulativeValue BreakPoint GroupName > A 2 2 10 1 > B 3 5 10 1 > C 2 7 10 1 > D 4 11 10 1 > E 5 5 10 2 > F 1 6 10 2 > G 1 7 10 2 > H 5 12 10 2 > I 11 11 10 3 > J 8 8 10 4 > K 2 2 10 4 > > I spent hours trying to update the CumulativeValue field untill the > BreakPoint value is crossed, and restarting the cumulative counter, but I > have too little sql knowledge to do this. > > Could anyone help me? > > thanks > > gert > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users