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
