[Solved] Progressive Sum in a column
-
- Posts: 89
- Joined: Tue Jul 16, 2013 2:24 pm
[Solved] Progressive Sum in a column
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.
Is it possible to obtain in a column of a query the progressive sum as shown in the figure? (Made with Calc).
Thank you.
Last edited by pinco pallo on Sat Sep 17, 2016 2:12 pm, edited 1 time in total.
LiBO 6.2.8.2 on macOS 10.12.6 (Sierra)
-
- Posts: 89
- Joined: Tue Jul 16, 2013 2:24 pm
Re: Progressive Sum in a column
I want to do this in Base. In Calc it is too easy
LiBO 6.2.8.2 on macOS 10.12.6 (Sierra)
Re: Progressive Sum in a column
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?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Posts: 89
- Joined: Tue Jul 16, 2013 2:24 pm
Re: Progressive Sum in a column
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.
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.
LiBO 6.2.8.2 on macOS 10.12.6 (Sierra)
Re: Progressive Sum in a column
Hello,
let's say you have a table called DATA
ColumnsThen this query will give you your result:
looks like
EDIT: Thanks to User Sliderule, who found a missing row in the result table, it's corrected now.
R
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)
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
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"
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
R
Last edited by F3K Total on Sat Sep 17, 2016 10:41 am, edited 1 time in total.
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
Re: Progressive Sum in a column
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 89
- Joined: Tue Jul 16, 2013 2:24 pm
Re: Progressive Sum in a column
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 ?F3K Total wrote:Then this query will give you your result: ....
Thanks and sorry for the trouble.
@Vllleroy Thanks for your answer, very interesting.
LiBO 6.2.8.2 on macOS 10.12.6 (Sierra)
Re: Progressive Sum in a column
Like this?Result:R
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"
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
- MMove 1.0.6
- Extension for easy, exact positioning of shapes, pictures, controls, frames ...
- my current system
- Windows 10 AOO, LOLinux Mint AOO, LO
-
- Posts: 89
- Joined: Tue Jul 16, 2013 2:24 pm
Re: Progressive Sum in a column
Of course, I tried to change + to - but it did not work. Maybe I was sleepy ..
Thanks again.
Thanks again.
LiBO 6.2.8.2 on macOS 10.12.6 (Sierra)