Firebird 4 - "SUM" with where clause lazarus / 16-Databases ---------------------------------------------------------------------- Nicole · 2023-03-23 19:17 this statement works fine and generates SUM correctly 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,EXIT_DATE, Ergebnis,' +      '(SELECT SUM(Ergebnis) FROM tbtrades y ' +         'FROM tbtrades x ' +      ' ORDER BY x.EXIT_DATE, x.id_trade '; this statemet figures wrong: 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,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 x ' +  ►►    'where ' + VonBisAlsString + ' and ' + KontowahlOhneWHERE + s +      ' ORDER BY x.EXIT_DATE, x.id_trade '; This may be due to the fact, that the latter has a where clause. How to do it better? I use firebird 4 and there shall be "aggregate functions". Unfortunately I did not find anything about combining them with "where". ---------------------------------------------------------------------- rvk · 2023-03-23 19:27 Quote from: Nicole on March 23, 2023, 08:17:22 pmThis may be due to the fact, that the latter has a where clause. How to do it better? If you show what VonBisAlsString, KontowahlOhneWHERE and s are, maybe we can tell. Maybe it's easier to do a Showmessage(ibquery.SQL.TEXT); just before the open. You can click the dialog and press CTRL+C to copy it to the clipboard and CTRL+V here in a post. Probably if you see the complete SQL it becomes evident why the error occurs. Also... ALWAYS mention the EXACT exception or error message. ---------------------------------------------------------------------- Zvoni · 2023-03-23 19:33 I would be more surprised if the first one works, since if my eyes are not deceiving me, there is a closing parentheses missing in line 2 If you told us with sampledata what you want to achieve i‘m pretty sure we can crack this nut. I always get a shiver seeing such SQL-Statements ---------------------------------------------------------------------- Nicole · 2023-03-23 19:53 Thank you for the answers. ... only: misunderstanding! Both statements work fine. The problem is "SUM". It is given back fine, - but in the second case with wrong figures. I give you an example. Let say the results are in case 1: 1, 2, 3, 4... and the sums are 1, 3, 6, 10 this is fine. In case 2 they are e.g. 1, 20, 12....? huh? I am not sure, how they are made of. I think it possible that SUM is figured without the where-clause. My question is now: How to put it, that SUM does only sum up results, which MATCH the where clause? IMHO it should not be important, what this where clauses have inside. The ugly way would be, that I put all my results into an array and sum it up one by one. Yes, this will work, but it is ugly. ---------------------------------------------------------------------- rvk · 2023-03-23 19:58 Quote from: Nicole on March 23, 2023, 08:53:56 pmHow to put it, that SUM does only sum up results, which MATCH the where clause? In that case you also need to do the same WHERE in the subselect (for SUM). Depending on if you really need the SUM() upto that date, you could also restructure the complete SQL more clearly. But that depends on what you really want as a result. ---------------------------------------------------------------------- d4eva · 2023-03-23 20:14 Works fine in postgres create table tbtrades(id_trade integer, EXIT_DATE date, Ergebnis money) insert into tbtrades values(1, CURRENT_DATE-50, 10); insert into tbtrades values(2, CURRENT_DATE-40, 20); insert into tbtrades values(3, CURRENT_DATE-30, 30); insert into tbtrades values(4, CURRENT_DATE-20, 40); insert into tbtrades values(5, CURRENT_DATE-10, 50); 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 x        ORDER BY x.EXIT_DATE, x.id_trade #   id_trade   exit_date   ergebnis   sum 1   1   2023-02-01   10,00 €   10,00 € 2   2   2023-02-11   20,00 €   30,00 € 3   3   2023-02-21   30,00 €   60,00 € 4   4   2023-03-03   40,00 €   100,00 € 5   5   2023-03-13   50,00 €   150,00 € ---------------------------------------------------------------------- rvk · 2023-03-23 20:17 Quote from: d4eva on March 23, 2023, 09:14:50 pmWorks fine in postgres@Nicole didn't show the WHERE from the main SELECT. If you do a WHERE there the numbers might not add up correctly anymore (because the total sum until that date is always taken). I already asked for the values of those 3 WHERE variables. ---------------------------------------------------------------------- d4eva · 2023-03-23 20:23 Quote from: rvk on March 23, 2023, 09:17:29 pmIf you do a WHERE there the numbers might not add up correctly anymore (because the total sum until that date is always taken). Yes, you're right. Then both queries must use the same custom where. ---------------------------------------------------------------------- Zvoni · 2023-03-24 08:02 Ahhh.... so this looks like a "running total" (HINT HINT HINT!!!) using d4eva's data 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, SUM(ergebnis) OVER(ORDER BY exit_date) AS SummeFROM tbtrades ORDER BY EXIT_DATELooks easier on the eyes... ---------------------------------------------------------------------- rvk · 2023-03-24 10:10 Quote from: Zvoni on March 24, 2023, 09:02:42 amusing d4eva's data 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, SUM(ergebnis) OVER(ORDER BY exit_date) AS SummeFROM tbtrades ORDER BY EXIT_DATELooks easier on the eyes... And just to put that into a little Fiddle  https://dbfiddle.uk/O2r3CsZ4 Mmm, that was for Firebird 3. Firebird 4 doesn't seem to like it: https://dbfiddle.uk/EFZVHCHc ---------------------------------------------------------------------- Zvoni · 2023-03-24 12:53 Quote from: rvk on March 24, 2023, 11:10:41 amMmm, that was for Firebird 3. Firebird 4 doesn't seem to like it: https://dbfiddle.uk/EFZVHCHc But it should: https://www.firebirdsql.org/file/documentation/html/en/ refdocs/fblangref40/firebird-40-language- reference.html#fblangref40-windowfuncs https://dbfiddle.uk/TyyPd7-b It doesn't like the Numeric-Datatype for Summing?!?!?!? "Ergebnis" changed to integer. works immediately ---------------------------------------------------------------------- rvk · 2023-03-24 13:10 Quote from: Zvoni on March 24, 2023, 01:53:50 pmIt doesn't like the Numeric-Datatype for Summing?!?!?!? "Ergebnis" changed to integer. works immediately Yikes. In Firebird 3 it did work. Maybe the size 12,2 is too big. Weird. Edit: Yes. NUMERIC(9,2) works. From NUMERIC(10,2) onwards it fails. https://dbfiddle.uk/KL_Ei2pf ---------------------------------------------------------------------- Zvoni · 2023-03-24 13:32 Quote from: rvk on March 24, 2023, 02:10:55 pmYikes. In Firebird 3 it did work. Maybe the size 12,2 is too big. Weird. Edit: Yes. NUMERIC(9,2) works. From NUMERIC(10,2) onwards it fails. https://dbfiddle.uk/KL_Ei2pf Huh? Seriously???? Could someone please explain in simple words WHY????? So, that way, i could never get a Result for 10,000,000.00 and up? Or is it that the dbfiddle cuts off for 32 byte limit? --> https://fir ebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref 40-datatypes-fixedtypes.html ("Defaults to 9") EDIT: And changing NUMERIC(9,2) to FLOAT works, too in FB4 ---------------------------------------------------------------------- Nicole · 2023-03-24 14:00 rvk probably saw the point (I supspect this as well) QuoteIf you do a WHERE there the numbers might not add up correctly anymore (because the total sum until that date is always taken). Some felt unease because I only posted my string-lines. Here is the complete SQL statement 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, 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 x where  (Entry_Date >='27.12.2022' and Exit_Date <= '31.03.2023') and  zuKonto = '4' ORDER BY x.EXIT_DATE, x.id_trade and: no, moving the ")" down, does not solve it. This SQL statement is legacy from Firebird 2.5. In the meanwhile I upgraded to Firebird 4.0. So there may be an advanced way to put it (which I do not know how to), which Firebird 2.5. would not have accepted. ---------------------------------------------------------------------- rvk · 2023-03-24 14:19 Quote from: Nicole on March 24, 2023, 03:00:06 pmCode: 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, 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 x where  (Entry_Date >='27.12.2022' and Exit_Date <= '31.03.2023') and  zuKonto = '4' ORDER BY x.EXIT_DATE, x.id_trade and: no, moving the ")" down, does not solve it. This SQL statement is legacy from Firebird 2.5. In the meanwhile I upgraded to Firebird 4.0. So there may be an advanced way to put it (which I do not know how to), which Firebird 2.5. would not have accepted. What exactly is the problem now? If you want the running total of ONLY zuKonto = 4 then you ALSO need to add " and  zuKonto = '4' " to your sub SELECT. 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 AND  zuKonto = '4' ) FROM tbtrades x WHERE  (Entry_Date >='27.12.2022' AND Exit_Date <= '31.03.2023') AND  zuKonto = '4' ORDER BY x.EXIT_DATE, x.id_trade Otherwise you need to tell exactly what you want. Fiddle example: https://dbfiddle.uk/cNe2267d ---------------------------------------------------------------------- Nicole · 2023-03-24 18:32 this figures exactly what I want. Thank you so much, you are my hero ---------------------------------------------------------------------- rvk · 2023-03-24 18:51 BTW This also counts the amounts of records before the Entry_Date in the sum(). If that's not wanted you also need to include that one in the where of the sub select ---------------------------------------------------------------------- Nicole · 2023-03-25 17:34 this is an extremely useful feature, not a bug. But I spoiled something. If you have time, it would be great, if you can read my very last posting.