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?
 
kretzschmarConnect With a Mentor Commented:
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
 
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
All Courses

From novice to tech pro — start learning today.