Calculat the sum of a column in DBGrid

Hello!

I have a DBGrid with some numeric fields, I want to calculate the sum of
the column when any data change, the change will not write to database until a key pressed, so I can't use another query to find the sum, could anyone give me some hints?

Thank.
GundamGP01Asked:
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 gundam,

why can't u use another-query?
do u use cachedupdates?

meikl
0
GundamGP01Author Commented:
Hi Meikl,

Yes, I use cachedupdates, so the data will not write to database until I applyupdate. At this time, the another-query doesn't got new data.

Gundam
0
kretzschmarCommented:
hi gundam,

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);
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

fkmfkmCommented:
That is very long...

Any simpler answer.. ?
0
fkmfkmCommented:
That is very long...

Any simpler answer.. ?

Cause I also have many problems.on this too...
0
kretzschmarCommented:
well yes,

there is another solution also,
in the afterpost and afterdelete event of the dataset iterate through all records and add the field, where the sum is wanted.

but, depending on the amount of records of the dataset, this will cost some more perfomance.

let me know, if a sample is needed for this

meikl
0
kretzschmarCommented:
gundam, are you here?
0
GundamGP01Author Commented:
Yes, I have try your solution, but having some problem

1. The field was a calculated field, without OldValue

2. Even if it is a data field, it will get the wrong result if modify more than onec. for example:

before change:
Fields[0].oldValue = 10.0, Fields[0].AsFloat = 10.0, sumval = 10.0,

if I change fields[0] to 20, the data will be:
Fields[0].oldValue = 10.0, Fields[0].AsFloat = 20.0, sumval = 10.0,

the sumval after calculation should be:
sumval := 10.0 - 10.0; sumval := 0 + 20.0 = 20 <- this is correct

if I change fields[0] to 15, the data will be:
Fields[0].oldValue = 10.0, Fields[0].AsFloat = 15.0, sumval = 20.0,

after calculation:
sumval := 20.0 - 10.0; sumval := 10 + 15.0 = 25 <- this is wrong!

because I am using CachedUpdates, the OldValue will not update after Post. Until I commitupdate.

I've think about your second solution, because I calculate this field in the oncalcfield event, it will be trigger when iterate through the records, which make my program loop infinitely.

Gundam
0
kretzschmarCommented:
well,

must do a bit finetuning,
to avoid such effects you've
mentioned above.

i let you know when i'm ready

meikl
0
kretzschmarCommented:
well ok,

here is it,
no more usage of the oldvalue-property

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.

hope that works now better

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
GundamGP01Author Commented:
Thank, I will test it.

Gundam
0
kretzschmarCommented:
hi gundam,
some results?
meikl
0
GundamGP01Author Commented:
Thank for your solution.

I have use your concept but make some modification.
Because the field is a calculated field, I record the old value,
before assign new value, so the variable are local.
Also, do the calculation in both BeforeDelete and BeforeCancel event.

Gundam
0
kretzschmarCommented:
hi gundam,

first thanks for accepting,
glad that you get it work
good luck again

meikl ;-)
0
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.