[Solved] Progressive Sum in a column

[pinco pallo]

Hi to all.
Is it possible to obtain in a column of a query the progressive sum as shown in the figure? (Made with Calc).
Thank you.

[pinco pallo]

I want to do this in Base. In Calc it is too easy

[FJCC]

I am not sure this can be done but I have a question while I think about possible answers. What determines the order of the returned results?

[pinco pallo]

Thanks.
I have 2 numeric fields in a table. In a query ì calculate the sum of them. I would another column with the progressive sum: the sum of previus row and current row. I hope I explained.

[F3K Total]

Hello,
let's say you have a table called DATA
Columns

• "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY
• "INPUT A" DECIMAL(10,2)
• "INPUT B" DECIMAL(10,2)

like this:

Code: Select all

ID   INPUT A   INPUT B
1     2.00     97.00
2    27.00     97.00
3    99.00      8.00
4    46.00     16.00
5    33.00      4.00
6    44.00     97.00
7     4.00     51.00
8    30.00     27.00
9    88.00     72.00
10   95.00     41.00
11    6.00     31.00
12   92.00     93.00

Then this query will give you your result:

Code: Select all

SELECT 
    X."ID",
    X."INPUT A",
    X."INPUT B",
    X."INPUT A" + X."INPUT B" AS "SUM",
    SUM(Y."INPUT A" + Y."INPUT B") AS "PROGESSIVE SUM"
FROM 
    "DATA" AS X 
JOIN 
    "DATA" AS Y 
ON 
    Y.ID <= X.ID
GROUP BY
    X."ID",
    X."INPUT A",
    X."INPUT B",
    "SUM"

looks like

Code: Select all

ID  INPUT A  INPUT B    SUM    PROGESSIVE SUM
1    2.00    97.00     99.00    99.00
2   27.00    97.00    124.00   223.00
3   99.00     8.00    107.00   330.00
4   46.00    16.00     62.00   392.00
5   33.00     4.00     37.00   429.00
6   44.00    97.00    141.00   570.00
7    4.00    51.00     55.00   625.00
8   30.00    27.00     57.00   682.00
9   88.00    72.00    160.00   842.00
10  95.00    41.00    136.00   978.00
11   6.00    31.00     37.00  1015.00
12  92.00    93.00    185.00  1200.00

EDIT: Thanks to User Sliderule, who found a missing row in the result table, it's corrected now.
R

[Villeroy]

And you can link a database range to the database data and let Calc do the job. Spreadsheet formulas adjacent to an import range update automatically.

[pinco pallo]

Then this query will give you your result: ....

Thank you, it works very well. But if I would to change from SUM to DIFFERENCE between INPUT A and INPUT B, the Progressive in the first row become wrong. How can I correct the code ?
Thanks and sorry for the trouble.

@Vllleroy Thanks for your answer, very interesting.

[F3K Total]

Like this?

Code: Select all

SELECT 
    X."ID",
    X."INPUT A",
    X."INPUT B",
    X."INPUT A" - X."INPUT B" AS "DIFFERENCE",
    SUM(Y."INPUT A" - Y."INPUT B") AS "PROGESSIVE SUM"
FROM 
    "DATA" AS X 
JOIN 
    "DATA" AS Y 
ON 
    Y.ID <= X.ID
GROUP BY
    X."ID",
    X."INPUT A",
    X."INPUT B",
    "SUM"

Result:

Code: Select all

ID  INPUT A  INPUT B   DIFF  PROGESSIVE SUM
1      2      97      -95   -95
2     27      97      -70   -165
3     99       8       91   -74
4     46      16       30   -44
5     33       4       29   -15
6     44      97      -53   -68
7      4      51      -47   -115
8     30      27        3   -112
9     88      72       16   -96
10    95      41       54   -42
11     6      31      -25   -67
12    92      93       -1   -68

R

[pinco pallo]

Of course, I tried to change + to - but it did not work. Maybe I was sleepy ..
Thanks again.