[solved] SQL trick for generating a series of sums lazarus / 16-Databases ---------------------------------------------------------------------- Nicole · 2022-10-28 16:12 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  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2022-10-28 16:55 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  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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/r nfb30-dml-windowfuncs.html ---------------------------------------------------------------------- LacaK · 2022-10-28 17:08 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 · 2022-10-28 17:16 Quote from: LacaK on October 28, 2022, 07:08:23 pmLook for SQL window functions. In your case it can be something like: SELECT myDate, SUM(addThem) OVER(ORDER BY myDate) AS RunningTotal FROM myTable 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 · 2022-10-28 17:57 Thank you for your answers I wrote Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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) ---------------------------------------------------------------------- MarkMLl · 2022-10-28 18:00 Quote from: Zvoni on October 28, 2022, 07:16:22 pmWindow-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) "Window" or "analytic" functions, depending on the server software's naming preference. In any case it's not a Pascal or Lazarus issue (unless for some reason the library functions can't process the result). I've used that sort of thing a fair amount, before which I piped PostgreSQL output into APL (which in some ways was easier). MarkMLl ---------------------------------------------------------------------- Zvoni · 2022-10-28 18:15 Quote from: Nicole on October 28, 2022, 07:57:59 pmThank you for your answers I wrote Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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) cast portion (or one value of the division) to float/double. I‘m guessing since your using division on two integers it return an integer Btw: you‘re trying an example which is not corresponding to your initial problem. Have you tried my „aircode“? EDIT2: just looked it up: don’t cast the result of the division to float/double. You must cast one partner to float/double BEFORE division. A shortcut is multiplying one partner with 1.0 SELECT ID, Ergebnis*1.0 / (SELECT sum(Ergebnis) From Table) As portion from table ---------------------------------------------------------------------- Nicole · 2022-10-28 18:46 I am afraid, I hardly understand the words, those nice guys here are using. Which 'air-code'? (sorry, I am 'overloaded') This '/' stands for division? Oh, dear. This is not, what I want. I wondered what this '/' means and thought, it stands for 2 ways of doing it or whatever. I just copied it without understanding anything. What I wanted to do is just a row of sums. (the input was 152, 152, -104, 33,....) Just add them one to the other. In the shown example, this would be 152, 304, 200, 233,.... instead of "zero". ---------------------------------------------------------------------- Zvoni · 2022-10-28 18:48 Quote from: Nicole on October 28, 2022, 08:46:25 pmI am afraid, I hardly understand the words, those nice guys here are using. Which 'air-code'? (sorry, I am 'overloaded') Aircode Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT myDate, addThem+LAG(addThem,1,0)OVER(ORDER BY myDate) AS MySum FROM myTable ---------------------------------------------------------------------- Zvoni · 2022-10-28 19:00 Quote from: LacaK on October 28, 2022, 07:08:23 pmLook for SQL window functions. In your case it can be something like: SELECT myDate, SUM(addThem) OVER(ORDER BY myDate) AS RunningTotal FROM myTable I apologize. You‘re right. Your sql gives the desired result. Nicole: LacaK gave the correct answer ---------------------------------------------------------------------- Nicole · 2022-10-28 19:13 Flamerobin does not want it. I wrote Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT id_trade,SUM(ERGEBNIS) OVER(ORDER BY id_trade) AS RunningTotalFROM TBTRADES I read "invalid token". This problem is not new to me. I see it, whenever I ask for a result, of which there is unclear, if it is one or many. select sum(ERGEBNIS) would be fine. ---------------------------------------------------------------------- rvk · 2022-10-28 19:27 Isn't this just a sub-select solution? Code: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT   myDate,  addThem,  (select sum(addThem) from tbASeriesOfInteger y   where y.myDate<=x.myDate)FROM tbASeriesOfInteger xorder by x.myDate  If there are more records for one date you'll need to work with an id field and use that for checking "<". ---------------------------------------------------------------------- Zvoni · 2022-10-28 19:28 Then i‘m out of ideas. I tested Lacak‘s solution in sqlite with your sample data in post 1, and out came the desired result. So maybe it‘s Flamerobin, or the neighbors dog barking up the wrong tree ---------------------------------------------------------------------- Zvoni · 2022-10-28 19:30 Quote from: rvk on October 28, 2022, 09:27:58 pmIsn't this just a sub- select solution? Code: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT   myDate,  addThem,  (select sum(addThem) from tbASeriesOfInteger y   where y.myDate<=x.myDate)FROM tbASeriesOfInteger xorder by x.myDate  If there are more records for one date you'll need to work with an id field and use that for checking "<". Rvk, yeah. That‘s one solution, but I’m curious since doc‘s claim FireBird understands the Window-Version of SUM ---------------------------------------------------------------------- rvk · 2022-10-28 19:37 Quote from: Zvoni on October 28, 2022, 09:30:22 pmRvk, yeah. That‘s one solution, but I’m curious since doc‘s claim FireBird understands the Window-Version of SUM Not until Firebird 3.0.6 (or at least some 3.x version) And I don't know which version TS is currently running. Probably 2.5.9. ---------------------------------------------------------------------- Zvoni · 2022-10-28 19:45 Quote from: rvk on October 28, 2022, 09:37:21 pmQuote from: Zvoni on October 28, 2022, 09:30:22 pmRvk, yeah. That‘s one solution, but I’m curious since doc‘s claim FireBird understands the Window-Version of SUM Not until Firebird 3.0.6 (or at least some 3.x version) And I don't know which version TS is currently running. Probably 2.5.9. Quote I use Firebird, which is SQL dialect 3*shrug* ---------------------------------------------------------------------- rvk · 2022-10-28 19:47 Quote from: Zvoni on October 28, 2022, 09:45:31 pmQuote I use Firebird, which is SQL dialect 3*shrug* Yeah, dialect is something different from version. I'm not sure when you ever use something different than dialect 3 in Firebird 1.5 and higher https://www.firebirdsql.org/pdfmanual/html/isql-dialects.html ---------------------------------------------------------------------- Zvoni · 2022-10-28 19:51 Quote from: rvk on October 28, 2022, 09:47:56 pmQuote from: Zvoni on October 28, 2022, 09:45:31 pmQuote I use Firebird, which is SQL dialect 3*shrug* Yeah, dialect is something different from version. I'm not sure when you ever use something different than dialect 3 in Firebird 1.5 and higher https://www.firebirdsql.org/pdfmanual/html/isql-dialects.html I know FireBird just from the name. Never worked with it. Is there something like SELECT VERSION() in FB? Then Nicole could be sure which version she actually uses ---------------------------------------------------------------------- rvk · 2022-10-28 19:54 Quote from: Zvoni on October 28, 2022, 09:51:35 pmIs there something like SELECT VERSION() in FB? Then Nicole could be sure which version she actually uses Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION')FROM rdb$database; But you can also see it in FlameRobin under the menus (get firebird server version or something). ---------------------------------------------------------------------- Zvoni · 2022-10-28 19:56 Well, let‘s see if Nicole can clear up the confusion. Don’t get me wrong: your solution works (tested in sqlite), but just looking at it, i think there is a performance penalty compared to a dedicated window-function ---------------------------------------------------------------------- MarkMLl · 2022-10-28 20:00 Quote from: rvk on October 28, 2022, 09:47:56 pmYeah, dialect is something different from version. ...and dialectic always triggers heated debate :-) MarkMLl ---------------------------------------------------------------------- rvk · 2022-10-28 20:01 Quote from: Zvoni on October 28, 2022, 09:56:35 pmDon’t get me wrong: your solution works (tested in sqlite), but just looking at it, i think there is a performance penalty compared to a dedicated window- function Definitely. In MySql you can loop through the records and keep a variable for sum (which is really neat) but you can't do that in Firebird. And window-function was introduced later on. I also still work with 2.5 in production so sometimes I need to get creative https://stackoverflow.com/questions/23947415/simulating-window- functions-in-firebird ---------------------------------------------------------------------- Nicole · 2022-10-29 08:18 Can somebody kindly explain what SHALL work with my table? The code imported into my FlameRobin was Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     SELECT id_trade,    SUM(ERGEBNIS) OVER(ORDER BY id_trade) AS RunningTotal    FROM TBTRADES May be the error is there? SQLite and dialect 3 (and mySQL and others...) differ slightly but under the line - summed up - in really many  ways. ---------------------------------------------------------------------- rvk · 2022-10-29 08:32 Quote from: Nicole on October 29, 2022, 10:18:50 amCan somebody kindly explain what SHALL work with my table? Read the last few posts please. You still haven't said with what version of Firebird you work. (Dialect is NOT the version.) If it's 2.5 then you should use my version i mentioned here https://forum.lazarus.freepascal.org/index.php/topic,61056.msg458294.h tml#msg458294 ---------------------------------------------------------------------- incendio · 2022-10-29 08:48 @Nicole, is this Firebird for Windows? If so, just go to firebird installation folder, look for fbclient.dll, right click it, and then choose properties . Under detail Tab you will find firebird version. ---------------------------------------------------------------------- Martin_fr · 2022-10-29 10:22 Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     SELECT id_trade,    SUM( SUM(addThem) ) OVER(ORDER BY myDate) AS RunningTotal    FROM TBTRADES     GROUP BY myDate  If dates are unique, then it is just Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     SELECT id_trade,    SUM( addThem ) OVER(ORDER BY myDate) AS RunningTotal    FROM TBTRADES ---------------------------------------------------------------------- Nicole · 2022-10-29 11:44 This is exactly the problem, my old Firebird. I now can remember, what Firedac (I do not have it any more in Lazarus) said in such cases: "multiple rows in singleton select" my Versions of Firebird: It is server version (local use) 2.5. für Win 7 64 bit. Years ago, I had anything with WOW.dll or so. It was hard to bring the thing to run at all and therefore I preferred it, not to touch it since long. Is there a way to do it for my old thing? If not, I will put this work at the end of my work, when I will migrate to Firebird embedded. I do not think, it is a good idea, to change all my code with a new version of Firebird at the same time. In other words: I want to finish the code-migration before. In other words, my question reads: Is there a way which makes sense for my old version, or shall I better post it for a day in the future after the upgrade of Firebird? ---------------------------------------------------------------------- rvk · 2022-10-29 11:48 Quote from: Nicole on October 29, 2022, 01:44:30 pmThis is exactly the problem, my old Firebird. So, have you tried my solution for Firebird 2.5 (with the sub-select)? (If not, please read the previous posts again) ---------------------------------------------------------------------- Nicole · 2022-10-29 12:12 I had not tried, because it was so hard to understand. But now it did, this looks as it would works sooner or later! Thank you so much. I wrote Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT  EXIT_DATE,  Ergebnis,  (select sum(Ergebnis) from tbtrades y   where y.EXIT_DATE <= x.EXIT_DATE)FROM tbtrades xorder by x.EXIT_DATE so why "sooner or later", - the sums do not work as I think, they should. The shall just add the sum, case a new "ergebnis" is written. (Ergebnis = German for result) I post a screenshot of the table (It is that great to have results at all after to many attempts with errors). and to add: Is it correct, that I can fill in more "wheres" in the last but one line? ---------------------------------------------------------------------- Nicole · 2022-10-29 12:18 a second attempt, this looks better from the results. But this would mean that the primary key must not have a different order than the date. Is this good style, - hm. ---------------------------------------------------------------------- Zvoni · 2022-10-29 12:28 You‘re having „duplicate“ dates. You have to sum the subselect, too, grouping by date (and ergebnis?) after the outside where ---------------------------------------------------------------------- rvk · 2022-10-29 13:10 Quote from: Nicole on October 29, 2022, 02:18:36 pma second attempt, this looks better from the results. But this would mean that the primary key must not have a different order than the date. Is this good style, - hm. Yes. Because you have multiple values per date you need the order by id_trade and the check for <= id_trade. So what you have now should work as you asked. Btw. I see two dates out if order. If you want to sort by date you can but then you need to include the date in the subselect and order by as well as the trade_id. ---------------------------------------------------------------------- rvk · 2022-10-29 13:17 So your SQL is correct now. But if you want to order by date (I see two dates out of order) you can do this; Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT  id_trade,  EXIT_DATE,  Ergebnis,  (SELECT SUM(Ergebnis) FROM tbtrades y   WHERE y.EXIT_DATE <= x.EXIT_DATE AND y.id_trade <= x.id_trade)FROM tbtrades xORDER BY x.EXIT_DATE, x.id_trade ---------------------------------------------------------------------- Nicole · 2022-10-29 15:16 This works!! Thank you so much. You save the basis for the new TAChart next week.