Show sum of a few consequtive records in front of the first one

[sahandsafizadeh.s]

I desire to show the following table in my report

 

   account       amount          sum

        1                  100              200

        1                   50                 -

        1                   50                 -

        2                   20                45

        2                   25                 -

 

As shown in the example above the sum column displays sum of amounts of a continuous series of records of same account. Like the first three records, their sum is 200 and it is displayed in the first row among them and the rest of the rows are left empty.

 

I'd appreciate any solutions

[yama818]

I have created a simple sample report.

The key points are as follows.
* Calculate 'sum' by aggregating 'amount' using groups
* Set 'Group' for 'Evaluation Time' of 'sum
* Set '$V{Group1_COUNT}==1' as a display condition for 'sum' to display only the first line

- Preview image

- Layout design image

- SQL

- Variable [sum]

- Text field [sum]

[jgust]

Here is how you can do it in SQL.

SELECT A.account     , A.amount     , case when a.rn = 1 then a.sum end as sumFROM (    select B.account         , B.amount         , sum(B.amount) over (PARTITION BY B.account) as sum         , ROW_NUMBER() OVER (PARTITION BY B.account order by b.account)  as rn    from (        select 1 as account, 100 as amount from dual        union all        select 1, 50 from dual        union all        select 1, 50 from dual        union all        select 2, 20 from dual        union all        select 2, 25 from dual    ) B) A[/code]

+-------+------+----+|ACCOUNT|AMOUNT|SUM |+-------+------+----+|1      |100   |200 ||1      |50    |    ||1      |50    |    ||2      |20    |45  ||2      |25    |    |+-------+------+----+[/code]