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