Recent

Author Topic: calculating total values for a field  (Read 13057 times)

davesimplewear

  • Sr. Member
  • ****
  • Posts: 319
    • Davids Freeware
calculating total values for a field
« on: October 01, 2009, 11:08:43 am »
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?
All things considered insanity seems the best option

Loesje

  • Full Member
  • ***
  • Posts: 168
    • Lazarus Support website
Re: calculating total values for a field
« Reply #1 on: October 03, 2009, 11:45:18 pm »
All sqldb/TbufDataset datasets supports calculated fields, and I think that ZEOS does that too

davesimplewear

  • Sr. Member
  • ****
  • Posts: 319
    • Davids Freeware
Re: calculating total values for a field
« Reply #2 on: October 04, 2009, 02:49:40 am »
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.
All things considered insanity seems the best option

davesimplewear

  • Sr. Member
  • ****
  • Posts: 319
    • Davids Freeware
Re: calculating total values for a field
« Reply #3 on: October 11, 2009, 02:41:18 am »
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.
All things considered insanity seems the best option

Zoran

  • Hero Member
  • *****
  • Posts: 1824
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: calculating total values for a field
« Reply #4 on: October 12, 2009, 12:55:03 pm »
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;
« Last Edit: October 12, 2009, 01:12:56 pm by zoran »

davesimplewear

  • Sr. Member
  • ****
  • Posts: 319
    • Davids Freeware
Re: calculating total values for a field
« Reply #5 on: October 13, 2009, 04:01:03 am »
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
All things considered insanity seems the best option

 

TinyPortal © 2005-2018