Forum > Databases
Firebird 4 - "SUM" with where clause
Nicole:
this statement works fine and generates SUM correctly
--- 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,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 [+][-]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,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:
--- 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?
--- End quote ---
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:
--- 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?
--- End quote ---
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.
Navigation
[0] Message Index
[#] Next page