Forum > Databases

[solved] SQL trick for generating a series of sums

(1/7) > >>

Nicole:
Assumed there is a table with these fields

myDate  addThem
1.10.2022 30
2.10.2022 40
3.10.2022 1

and I like to add them so will give me

--- Code: MySQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---select SUM (addThem) from tbASeriesOfInteger71, easy

How can I add every date?
What can I ask to get these results:
1.10.2022 30
2.10.2022 70
3.10.2022 71

What I want is a list of sums for every new date.
I use Firebird, which is SQL dialect 3

Thank you in advance.

Zvoni:
Sorry, don’t understand.
Where are the values for „addThem“ come from?
Are those arbitrary values? Are they values in a second column?

EDIT: on second look i think i understand.
You want to add up „addThem“ per row with the previous row.
No idea about Firebird, but i‘d check if Lag/Leap-Functions are available in FB‘s SQL-dialect.
Here how you use it in MS SQL-Server
https://www.sqlshack.com/sql-lag-function-overview-and-examples/

Aircode

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT myDate, addThem+LAG(addThem,1,0)OVER(ORDER BY myDate) AS MySum FROM myTable 
EDIT2: looks like Firebird supports Lag
https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-dml-windowfuncs.html

LacaK:
Look for SQL window functions. In your case it can be something like:

SELECT myDate, SUM(addThem) OVER(ORDER BY myDate) AS RunningTotal
FROM myTable

Zvoni:

--- Quote from: LacaK on October 28, 2022, 07:08:23 pm ---Look for SQL window functions. In your case it can be something like:

SELECT myDate, SUM(addThem) OVER(ORDER BY myDate) AS RunningTotal
FROM myTable

--- End quote ---
Window-Function yes, your sample no.
It would return the total Sum of the frame/window.
Usually you use that for partial sums (like you might know from Excel)

Nicole:
Thank you for your answers

I wrote

--- Code: MySQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT id_trade,ERGEBNIS, ERGEBNIS / (select sum(ERGEBNIS) from tbtrades) portionFROM TBTRADES
but end up by zero-values.
What is wrong?
See screenshot

(I have not understood anything of what I did, I just used the link and tried)

Navigation

[0] Message Index

[#] Next page