Fast sum calculation without aggregate from developer

Classic Servoy
1

I need to calculate the sum of a column, but I have to make a restriction to the calcution, so I can’t use the aggregate function from the Servoy Developer for the module. I tried a calculation in the Servoy Developer, but as far as I see, it is related to the currently selected record. Then I tried it using a form variable and getFoundSetCount to calculate the sum if the form is shown, but that is extremely slow. My next try is using a QBSelect, but I don’t know, how to apply the column.name.sum in the QBSelect.

How can I get that working and is that a fast sum calculation? If not fast, how can I calculate the sum in a fast way?

2

Hi Sebastiaan,

What are you trying to do exactly ?

3

ROCLASI:
Hi Sebastiaan,

What are you trying to do exactly ?

Hello Robert, in SQL translated, I want to do the following.:

SELECT SUM( gross ) AS grossSumInvoice
FROM bills
WHERE billType = 1;

SELECT SUM( gross ) AS grossSumCreditNote
FROM bills
WHERE billType = 2;

grossSum = grossSumInvoice - grossSumCreditNote

Can I do that using a QBSelect? How can I formulate the SELECT part for the QBSelect?

4

Hi Sebastian,

This is absolute generic SQL syntax so why the need for QBSelect (which purpose is to provide an abstraction from any vendor specific SQL).
So I would just use SQL and a getDataSetByQuery();
To be honest I don’t use the QB syntax at all. I haven’t had a need for it.

Hope this helps.

5

ROCLASI:
Hi Sebastian,

This is absolute generic SQL syntax so why the need for QBSelect (which purpose is to provide an abstraction from any vendor specific SQL).
So I would just use SQL and a getDataSetByQuery();
To be honest I don’t use the QB syntax at all. I haven’t had a need for it.

Hope this helps.

Yes, I use it, because it is independent from the database vendor. So, I want to avoid the use of SQL in the code.

6

Hi Sebastian,

All I am saying is that that SQL is already generic and therefor vendor independent.
Anyway, I looked it up (since I don’t use it) and the syntax to add an aggregate is the following:

var _oQuery = databaseManager.createSelect('db:/connectionName/bills'),
    _ds;

_oQuery.result.add(_oQuery.getColumn('gross').sum,'grossSumInvoice');
_ds = databaseManager.getDataSetByQuery(_oQuery,1);

Hope this helps.