Problem in getting running total with group by
Hi,
How to group for running total.
My Table Script:
create table test_running
(
cardno varchar(4),
iodate date,
iostatus varchar(10),
iotime varchar(8),
flag varchar(1)
);
insert into test_running values ('0001','2013-01-01','Entry','09:10:10','1');
insert into test_running values ('0001','2013-01-01','Exit','09:20:10','0');
insert into test_running values ('0001','2013-01-01','Entry','09:30:10','1');
insert into test_running values ('0001','2013-01-01','Exit','09:40:10','0');
insert into test_running values ('0001','2013-01-01','Entry','09:50:10','1');
insert into test_running values ('0001','2013-01-01','Exit','09:60:10','0');
insert into test_running values ('0001','2013-01-01','Entry','10:10:10','1');
insert into test_running values ('0001','2013-01-01','Exit','10:40:10','0');
insert into test_running values ('0001','2013-01-01','Entry','10:40:10','1');
insert into test_running values ('0001','2013-01-02','Entry','09:30:10','1');
select * from test_running;
select cardno,
iodate,
iotime,
iostatus,
flag,
(@runtot := a.flag + @runtot) AS rt, ( @runtot := a.flag ) ne
from test_running a,(SELECT @runtot:=0) c
order by cardno,iodate,iotime;
Result set:
cardno iodate iotime iostatus flag rt ne
1 01-01-2013 09:10:10 Entry 1 1 1
1 01-01-2013 09:20:10 Exit 0 1 0
1 01-01-2013 09:30:10 Entry 1 1 1
1 01-01-2013 09:40:10 Exit 0 1 0
1 01-01-2013 09:50:10 Entry 1 1 1
1 01-01-2013 0.416782407 Exit 0 1 0
1 01-01-2013 10:10:10 Entry 1 1 1
1 01-01-2013 10:40:10 Exit 0 1 0
1 01-01-2013 10:40:10 Entry 1 1 1
1 02-01-2013 09:30:10 Entry 1 2 1
Expected resultset:
cardno iodate iotime iostatus flag rt ne
1 01-01-2013 09:10:10 Entry 1 1 1
1 01-01-2013 09:20:10 Exit 0 1 0
1 01-01-2013 09:30:10 Entry 1 1 1
1 01-01-2013 09:40:10 Exit 0 1 0
1 01-01-2013 09:50:10 Entry 1 1 1
1 01-01-2013 0.416782407 Exit 0 1 0
1 01-01-2013 10:10:10 Entry 1 1 1
1 01-01-2013 10:40:10 Exit 0 1 0
1 01-01-2013 10:40:10 Entry 1 1 1
1 02-01-2013 09:30:10 Entry 1 1 1
Kindly help.