Page 1 of 1
Aggregates / Sum of a Field of all Records
Posted: Wed 13 Feb 2013 13:40
by 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;
Re: Aggregates / Sum of a Field of all Records
Posted: Wed 13 Feb 2013 15:12
by 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 )
Re: Aggregates / Sum of a Field of all Records
Posted: Wed 13 Feb 2013 17:01
by 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.
Re: Aggregates / Sum of a Field of all Records
Posted: Thu 14 Feb 2013 13:39
by 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.
Re: Aggregates / Sum of a Field of all Records
Posted: Tue 05 Mar 2013 07:13
by dschuch
Re: Aggregates / Sum of a Field of all Records
Posted: Tue 05 Mar 2013 11:56
by 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.