Firebird 4 - "SUM" with where clause

[Nicole]

this statement works fine and generates SUM correctly

Code: MySQL  [Select][+][-]

1.      'SELECT id_trade,EXIT_DATE, Ergebnis,' +
2.       '(SELECT SUM(Ergebnis) FROM tbtrades y ' +  
3.       'FROM tbtrades x ' +
4.       ' ORDER BY x.EXIT_DATE, x.id_trade ';

this statemet figures wrong:

Code: MySQL  [Select][+][-]

1.      'SELECT id_trade,EXIT_DATE, Ergebnis,' +
2.       '(SELECT SUM(Ergebnis) FROM tbtrades y ' +  
3.  ►►     'WHERE y.EXIT_DATE <= x.EXIT_DATE AND y.id_trade <= x.id_trade) ' +
4.       'FROM tbtrades x ' +
5.   ►►    'where ' + VonBisAlsString + ' and ' + KontowahlOhneWHERE + s +
6.       ' 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]

This 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]

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]

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]

How 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]

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]

Works 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]

If 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]

Ahhh.... so this looks like a "running total" (HINT HINT HINT!!!)

using d4eva's data

Code: SQL  [Select][+][-]

1. SELECT id_trade,EXIT_DATE,ergebnis, SUM(ergebnis) OVER(ORDER BY exit_date) AS Summe
2. FROM tbtrades ORDER BY EXIT_DATE

Looks easier on the eyes...

[rvk]

using d4eva's data

Code: SQL  [Select][+][-]

1. SELECT id_trade,EXIT_DATE,ergebnis, SUM(ergebnis) OVER(ORDER BY exit_date) AS Summe
2. FROM tbtrades ORDER BY EXIT_DATE

Looks 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]

Mmm, 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]

It 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]

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

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://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-datatypes-fixedtypes.html ("Defaults to 9")

EDIT: And changing NUMERIC(9,2) to FLOAT works, too in FB4

[Nicole]

rvk probably saw the point (I supspect this as well)

If 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][+][-]

1. SELECT id_trade, EXIT_DATE, Ergebnis,
2. (
3. SELECT SUM(Ergebnis)
4. FROM tbtrades y
5.  
6. WHERE y.EXIT_DATE <= x.EXIT_DATE AND y.id_trade <= x.id_trade
7. )
8. FROM tbtrades x
9. where  (Entry_Date >='27.12.2022' and Exit_Date <= '31.03.2023') and  zuKonto = '4'
10. 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]

Code: MySQL  [Select][+][-]

1. SELECT id_trade, EXIT_DATE, Ergebnis,
2. (
3. SELECT SUM(Ergebnis)
4. FROM tbtrades y
5. WHERE y.EXIT_DATE <= x.EXIT_DATE AND y.id_trade <= x.id_trade
6. )
7. FROM tbtrades x
8. where  (Entry_Date >='27.12.2022' and Exit_Date <= '31.03.2023') and  zuKonto = '4'
9. 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][+][-]

1. SELECT id_trade, EXIT_DATE, Ergebnis,
2. (
3. SELECT SUM(Ergebnis)
4. FROM tbtrades y
5. WHERE y.EXIT_DATE <= x.EXIT_DATE AND y.id_trade <= x.id_trade AND  zuKonto = '4'
6. )
7. FROM tbtrades x
8. WHERE  (Entry_Date >='27.12.2022' AND Exit_Date <= '31.03.2023') AND  zuKonto = '4'
9. ORDER BY x.EXIT_DATE, x.id_trade

Otherwise you need to tell exactly what you want.

Fiddle example: https://dbfiddle.uk/cNe2267d