Display Sum of a DBgrid Column

I have a dbgrid linked to a Tquery with cached update set to true.

Now what I want is to display a total amount below the grid so that everytime I change the amount field (in my tquery), the total will also be updated.

How can I do this aside from using loops? I don't want to use loops because i'm using a dbgrid and that it would mean that everytime i change the field, the loop executes and the record pointer moves from first to bottom.

Is there any function which I can simply call that would automatically computer for the total.
onioncubeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
hi onioncube,

a sample how could it be done

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Mask, DBCtrls, DBTables, Grids, DBGrids, Db;

type
  TForm1 = class(TForm)
    Table1: TTable;
    DataSource1: TDataSource;
    Query1: TQuery;
    DBGrid1: TDBGrid;
    DataSource2: TDataSource;
    Button1: TButton;
    Button2: TButton;
    Edit1: TEdit;
    procedure FormCreate(Sender: TObject);
    procedure Table1BeforePost(DataSet: TDataSet);
    procedure Table1BeforeDelete(DataSet: TDataSet);
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

var sumval : double;  //holds the sum

procedure TForm1.FormCreate(Sender: TObject);
(sql = select sum(OfField) from table [where...])

begin
  query1.open;
  sumval := query1.Fields[0].AsFloat;  //get initial sum with a query
  edit1.text := floattostr(sumval);
  query1.close;
end;

//Recalculate sum, you could also use the fieldbyname-method
procedure TForm1.Table1BeforePost(DataSet: TDataSet);
begin
  if (table1.State = dsedit) then
  begin
    sumval := sumval - Table1.Fields[0].OldValue;
    sumval := sumval + Table1.Fields[0].AsFloat;
  end;
  if table1.state = dsinsert then
    sumval := sumval + Table1.Fields[0].AsFloat;
  edit1.text := floattostr(sumval);
end;

//Recalculate sum
procedure TForm1.Table1BeforeDelete(DataSet: TDataSet);
begin
  sumval := sumval - Table1.Fields[0].AsFloat;
  edit1.text := floattostr(sumval);
end;

end.

hope this helps

meikl
0
kretzschmarCommented:
hi onioncube,

better version

unit cach_upd_sum_u;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, DBTables, Db, Grids, DBGrids;

type
  TForm1 = class(TForm)
    Edit1: TEdit;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Table1: TTable;
    Query1: TQuery;
    Button1: TButton;
    procedure FormCreate(Sender: TObject);
    procedure Table1BeforePost(DataSet: TDataSet);
    procedure Table1BeforeDelete(DataSet: TDataSet);
    procedure Button1Click(Sender: TObject);
    procedure Table1BeforeEdit(DataSet: TDataSet);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

var
  sumval : Double;
  EdVal  : Double;

procedure TForm1.FormCreate(Sender: TObject);
//sql : select sum(wert) from Table
begin
  query1.open;
  sumval := query1.Fields[0].AsFloat;  //get initial sum with a query
  edit1.text := floattostr(sumval);
  query1.close;
end;

//Recalculate sum, you could also use the fieldbyname-method
procedure TForm1.Table1BeforePost(DataSet: TDataSet);
begin
  if (table1.State = dsedit) then
  begin
    sumval := sumval - EdVal;
    sumval := sumval + Table1.FieldByName('Wert').AsFloat;
  end;
  if table1.state = dsinsert then
    sumval := sumval + Table1.FieldByName('Wert').AsFloat;
  edit1.text := floattostr(sumval);
end;

//Recalculate sum
procedure TForm1.Table1BeforeDelete(DataSet: TDataSet);
begin
  sumval := sumval - Table1.FieldByName('Wert').AsFloat;
  edit1.text := floattostr(sumval);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  table1.ApplyUpdates;
  table1.CommitUpdates;
  formcreate(self);  //requery
end;

//remember currentvalue
procedure TForm1.Table1BeforeEdit(DataSet: TDataSet);
begin
  EdVal :=  Table1.FieldByName('Wert').AsFloat;
end;

end.

meikl
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.