MySQL Forums
Forum List  »  Newbie

Problem in getting running total with group by
Posted by: Punitha Ramesh
Date: February 06, 2014 05:42AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Problem in getting running total with group by
February 06, 2014 05:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.