A simple running total in Cache SQL

Question

Question

Alistair Purse · Oct 28, 2019

#SQL #Caché

Can anyone tell me how to add a simple running total in Cache SQL.

I'm selecting a quantity in the first column and want a running total in the second:

9 , 9

2 , 11

7 , 18

Discussion (9)0

Log in or sign up to continue

Robert Cemper · Oct 28, 2019

and with ROW number

select %VID as Row ,* from (
   SELECT  age ,
    (Select sum(i.age) from sample.person i 
     where i.id <= o.id and age < 20 ) as SubTotal
   FROM sample.person o 
   where age < 20

)

1 0

score 0 · Answer 1 · 2019-10-28T06:08:24-04:00

it seems the only way to do this is the old school method of joining back to the same table. was hoping for an equivalent to the OVER clause you get in SQL Server.

if anyone knows how to get a rownumber for each row I'd also like to know that.

score 0 · Answer 2 · 2019-10-28T10:04:50-04:00

We're working on support for OVER syntax (as part of SQL window functions) for a future release of IRIS. Feel free to reach out to me directly with more details on the specific requirements you have to see if they correspond to the feature set currently in the pipeline.

score 0 · Answer 3 · 2019-10-28T06:35:27-04:00

thanks Robert, I got this working by joining to the same table with a >= (as opposed to subquery) but same idea.

score 0 · Answer 4 · 2019-10-28T11:58:06-04:00

Christopher,

Your cursor method was pretty close to how I've ended up solving my task.

The problem I was trying to solve involved comparing a comulativeTotal with a previouscomulativeTotal. I had a go at trying to do this in SQL this morning but due to the fact I'd be joining back to the same table once to get the comulative total and yet again to get back to the previouscomulative total it meant the query took ages. I went with a embedded cursor in object script and created a recordset at the end to pass back both comulativeTotal and previouscomulativeTotal at the same time. The iterative approach worked much better here as the two totals were close (one iteration away) ..

score 1 · Answer 5 · 2019-10-28T06:22:31-04:00

As your table is not available I took a Table from SAMPLES using a subselect

SELECT age ,
     (Select top all sum(i.age) from sample.person i 
      where i.id <= o.id and age < 20 ) as SubTotal
FROM sample.person o 
where age < 20

score 0 · Answer 6 · 2019-10-28T06:53:01-04:00

Robert Cemper · Oct 28, 2019

0 0

score 0 · Answer 7 · 2019-10-28T14:01:23-04:00

Cursors work, but are needlessly complex imo. I liked RCemper's pure sql solution. Another (better) approach is to do your calculation on the application server to save the cpu on the data server for serving the data.

score 0 · Answer 8 · 2019-10-28T10:50:49-04:00

drop table if exists [stair];
create table [stair] (
[purse] integer
);
insert into [stair] (purse) values(9);
insert into [stair] (purse) values(2);
insert into [stair] (purse) values(7);
--++++++
declare cursorA cursor
for select purse from [dbo].[stair]
declare @col integer
declare @tot integer
open cursorA
set @tot = 0
fetch next from cursorA into @col
while @@FETCH_STATUS=0
begin
set @tot = @tot + @col;
print convert(varchar,@col) + ', ' + convert(varchar,@tot);
fetch next from cursorA INTO @col;
end
close cursorA;
deallocate cursorA;
drop table [stair];