calculating total values for a field
lazarus / 16-Databases

----------------------------------------------------------------------
davesimplewear · 2009-10-01 09:08

in delphi I used to use Tvolga Database which had a function to
calculate the total value of a field in a table, the following snippet
of code demonstrates what I am talking about: edBankBal.Value :=
Cash.Bal.GetSum('StartBal'); (Cash is a datamodule, Bal a table,
Getsum('StartBal') is the function to calculate  the total on field
StartBal), is there anything in Sqlite3 or TDBF or any database tables
available to lazarus that would do anything like this?

----------------------------------------------------------------------
Loesje · 2009-10-03 21:45

All sqldb/TbufDataset datasets supports calculated fields, and I think
that ZEOS does that too

----------------------------------------------------------------------
davesimplewear · 2009-10-04 00:49

Thank you for your answer I have seen the calculated fields in the
datasets but I don't know how they work, could someone provide a
sample please.

----------------------------------------------------------------------
davesimplewear · 2009-10-11 00:41

Is there someone that can give anexample or point me in the right
direction? the google and wiki searches have given me nothing, so I am
looking for some help, thank you.

----------------------------------------------------------------------
Zoran · 2009-10-12 10:55

I used calculated fields in Delphi, but only to calculate values from
diferent columns of the same row. I do not think that calculated
fields are intented to do calculations of more rows.

For example, if you have fields Amount1 and Amount2 in a table, you
could define calculated field AverageAmount and in query's
OnCalcFields event put something like this:
Code: [Select]Query1.FieldByName('AverageAmount').AsFloat := 0.5 *
(Query1.FieldByName('Amount1').AsFloat +
Query1.FieldByName('Amount2').AsFloat);

However, I would not use calculated fields for calculating sum of one
column in a whole table (and, if I understood you well, that is what
you want to do), but I would do some coding:
Code: [Select]var
  SumField1: Integer;

...

SumField1 := 0;
Query1.DisableControls;
try
  Query1.First;
  while not Query1.EOF do begin
    SumField1 := SumField1 + Query1.FieldByName(Field1).AsInteger;
    Query1.Next;
  end;
finally
  Query1.EnableControls;
end;

Or, you could define a separate query which gives you sum:
Code: [Select]var
  SumField1: Integer;
  Q: TZReadOnlyQuery; //-- that's if you use Zeos,
{ or Q: TSQLQuery if you use SQLdb components.}

....

Q := TZReadOnlyQuery.Create(Self); // or TSQLQuery.Create(Self);
try
  Q.Connection := ZConnection1; // or Q.Database := ...
  Q.SQL.Clear;
  Q.SQL.Append('select sum(Field1) as SumF1 from Table1');
  Q.Open;
  if not Q.IsEmpty then begin
    Q.First;
    SumField1 := Q.FieldByName(SumF1).AsInteger;
  end else
    SumField1 := 0;
finally
  Q.Free;
end;

----------------------------------------------------------------------
davesimplewear · 2009-10-13 02:01

Thank you zoran, the sum function would be the way to do what I want
frm the look of it, will give it a try.

Regards
david