Aggregates / Sum of a Field of all Records

[dschuch]

Hi,

like descriped here (and in the help) Aggregate Fields arent supported.
http://forums.devart.com/viewtopic.php? ... eld#p61550

So what is the best way, if you need the sum of a field of all records of a dataset?

is there sth around using "DisableControls, First, Next" etc? I do not want to use this way, because events like afterscroll are fired, etc.

currently my code looks like:

Code: Select all

 R:=RecNo;
 Result:=0;
 DisableControls;
 S:=AfterScroll;
 AfterScroll:=Nil;
 First;
 try
  while not EoF do begin
           Result:=Result+FieldByName(SumFieldName).AsFloat;
        Next;
  End;
 finally
  RecNo:=R;
  AfterScroll:=S;
  EnableControls;
 end;

[AlexP]

Hello,

Aggregate fields are not supported either in UniDAC or in PgDAC. To retrieve the sum by a field, you can use window functions of the PostgreSQL server. For example, the following query will return all the records from the EMP table and the common sum by the SAL field:

Code: Select all

SELECT empno, ename, sal, sum(sal) OVER () FROM emp

You can find more detailed information on window functions in the PostgreSQL documentation ( http://www.postgresql.org/docs/9.2/stat ... indow.html )

[dschuch]

hi, thanks, yes - using this very often.

but e.g. if you need to sum a calculated field you have to loop over your dataset. (or a client filter) so perhaps there is a way to use some kind of internal "hack" to loop over the row values without using the standard way (next/prior).

e.g. in DevExpress i can get a rowValue with the currentRow or via

DataController.Values[RecordNumber, Field]

so i can get my result [run through all records] without moving the current record (next/prior), so i do not have to look for side effects of the frontend (scrolling, enable/disable controls etc). i can just run over all records in a simple loop.

[AlexP]

Hello,

To retrieve the field value, you should move to a needed record, therefore, to retrieve all values of a field, you should run through all records of DataSet. We didn't deal with internal behaviour of DevExpress components, however, to get the behaviour you have described, most probably, DevExpress also uses the DisableControls and EnableControls methods to move to a needed record not displaying scrolling. Otherwise, DevExpress can have its own cache for data storing and access, from which data is retrieved by the methods you have described.

[dschuch]

it's already here:

http://devart.uservoice.com/forums/1046 ... entdataset

see 1).

[AlexP]

Hello,

Yes we plan to implement this feature for both TVirtualTable and other our DataSets, however, we cannot nominate the exact terms of the implementation.