Print Page - Firebird 4 - "SUM" with where clause

Programming => Databases => Topic started by: Nicole on March 23, 2023, 08:17:22 pm

Title: Firebird 4 - "SUM" with where clause
Post by: Nicole on March 23, 2023, 08:17:22 pm

this statement works fine and generates SUM correctly

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

     '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".

Title: Re: Firebird 4 - "SUM" with where clause
Post by: rvk on March 23, 2023, 08:27:32 pm

Quote from: Nicole on March 23, 2023, 08:17:22 pm

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.

Title: Re: Firebird 4 - "SUM" with where clause
Post by: Zvoni on March 23, 2023, 08:33:17 pm

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

Title: Re: Firebird 4 - "SUM" with where clause
Post by: Nicole on March 23, 2023, 08:53:56 pm

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.

Title: Re: Firebird 4 - "SUM" with where clause
Post by: rvk on March 23, 2023, 08:58:23 pm

Quote from: Nicole on March 23, 2023, 08:53:56 pm

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.

Title: Re: Firebird 4 - "SUM" with where clause
Post by: d4eva on March 23, 2023, 09:14:50 pm

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 €

Title: Re: Firebird 4 - "SUM" with where clause
Post by: rvk on March 23, 2023, 09:17:29 pm

Quote from: d4eva on March 23, 2023, 09:14:50 pm

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.

Title: Re: Firebird 4 - "SUM" with where clause
Post by: d4eva on March 23, 2023, 09:23:48 pm

Quote from: rvk on March 23, 2023, 09:17:29 pm

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.

Title: Re: Firebird 4 - "SUM" with where clause
Post by: Zvoni on March 24, 2023, 09:02:42 am

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

using d4eva's data

Code: SQL [Select][+]

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

Looks easier on the eyes...

Title: Re: Firebird 4 - "SUM" with where clause
Post by: rvk on March 24, 2023, 11:10:41 am

Quote from: Zvoni on March 24, 2023, 09:02:42 am

using d4eva's data
Code: SQL [Select][+][-]
SELECT id_trade,EXIT_DATE,ergebnis, SUM(ergebnis) OVER(ORDER BY exit_date) AS Summe
FROM tbtrades ORDER BY EXIT_DATE
Looks easier on the eyes...

And just to put that into a little Fiddle :D
https://dbfiddle.uk/O2r3CsZ4

Mmm, that was for Firebird 3.
Firebird 4 doesn't seem to like it:
https://dbfiddle.uk/EFZVHCHc

Title: Re: Firebird 4 - "SUM" with where clause
Post by: Zvoni on March 24, 2023, 01:53:50 pm

Quote from: rvk on March 24, 2023, 11:10:41 am

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

Title: Re: Firebird 4 - "SUM" with where clause
Post by: rvk on March 24, 2023, 02:10:55 pm

Quote from: Zvoni on March 24, 2023, 01:53:50 pm

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

Title: Re: Firebird 4 - "SUM" with where clause
Post by: Zvoni on March 24, 2023, 02:32:53 pm

Quote from: rvk on March 24, 2023, 02:10:55 pm

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

Title: Re: Firebird 4 - "SUM" with where clause
Post by: Nicole on March 24, 2023, 03:00:06 pm

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

Quote

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

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.

Title: Re: Firebird 4 - "SUM" with where clause
Post by: rvk on March 24, 2023, 03:19:34 pm

Quote from: Nicole on March 24, 2023, 03:00:06 pm

Code: MySQL [Select][+][-]
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][+]

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

Title: [solved] Re: Firebird 4 - "SUM" with where clause
Post by: Nicole on March 24, 2023, 07:32:08 pm

this figures exactly what I want.
Thank you so much, you are my hero

Title: Re: Firebird 4 - "SUM" with where clause
Post by: rvk on March 24, 2023, 07:51:49 pm

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

Title: Re: Firebird 4 - "SUM" with where clause
Post by: Nicole on March 25, 2023, 06:34:27 pm

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.

Lazarus

Programming => Databases => Topic started by: Nicole on March 23, 2023, 08:17:22 pm