Link to home
Start Free TrialLog in
Avatar of TG-Steve
TG-Steve

asked on

Delphi Tquery data handling

Hi Guys

within my form i have a tquery, tdatasource, tdatabase and dbgrid. when i run a given query it will feed back that data to the dbgrid. my question is that of the several columns of data displayed their is for example one column that contains cost prices. is there a way that upon the data being received that a given column can be totalled up and displayed within a label or similer on the form. that way giving me a total price of goods rather than adding up 100+ lines manually.

The connection type that this uses is BDE and sql server querying
SOLUTION
Avatar of Emmanuel PASQUIER
Emmanuel PASQUIER
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TG-Steve
TG-Steve

ASKER

epasquier

if you dont mind me asking do you have an example of the 2nd option please
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oh, yes, once you have that query working, the calculated answer doesn't have to appear in the grid but the TEdit control can be assigned that value.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>network bandwidth and memory
epasquier, nowadays, the databases are very powerfull

if you are on a oracle db you can also use analytical functions

if you're query is

select * from tableA

select *, sum(columnX) over (order by columnX) sum_columnX
from tableA

select *,
  sum(columnX) over (order by columnX) sum_columnX,
  count(columnX) over (order by columnX) count_columnX,
from tableA


or there is a other way:
use a devexpress grid, instead of a dbgrid
you can add the value you want in the grid footer for (count, avg, sum, max, min, ...)
no programming required
http://www.devexpress.com/Products/VCL/ExQuantumGrid/
Code not tested:

var
  Sum : Double;
begin
  Sum := 0;
  Query1.First;
  While Query1.Eof = false do
  begin
    Sum := Sum + Query1.FieldByName('Price').AsCurrency;
    Query1.next;
  end;

  LblSum := CurrencyToStr(Sum)

end;
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
perhaps this :

unit Unit1_Q_21293793;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics,
  Controls, Forms, Dialogs, DB, DBTables, Grids, DBGrids,
  Buttons, StdCtrls, ExtCtrls, DBCtrls, ComObj, ADODB, ToolWin, ComCtrls,
  Spin, Calendar;

type
  TForm1 = class(TForm)
      dsaLIST: TDataSource;
      ADOQueryTEST: TADOQuery;
      ToolBar: TToolBar;
      DBNavigator: TDBNavigator;
      ADOConnection: TADOConnection;
      DBGrid1: TDBGrid;
      ADOCommand: TADOCommand;
      Edit1: TEdit;
    Label1: TLabel;
      procedure FormCreate(Sender: TObject);
      procedure FormDestroy(Sender: TObject);
      procedure DBGrid1CellClick(Column: TColumn);
    private{ Private declarations }
    public { Public declarations }
      ConnStr:   string;
      function  GetSum: Currency;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
var
  FN:     string;
begin
  FN := ExtractFilePath(Application.ExeName) + 'DATABASE.MDB';
  ConnStr := 'Data Source=' + FN + ';Provider=Microsoft.Jet.OLEDB.4.0';
  ADOConnection.Close;
  ADOConnection.ConnectionString := ConnStr;
  ADOConnection.Open;
  ADOQueryTEST.Connection := AdoConnection;
  ADOQueryTEST.Active := True;
  ADOQueryTEST.FieldByName('ID').Visible := False;
end;

procedure TForm1.FormDestroy(Sender: TObject);
var
  I:      Integer;
begin
  for I := 0 to ADOConnection.DataSetCount-1 do
    ADOConnection.DataSets[I].Active := False;
end;

function  TForm1.GetSum: Currency;
var
  I:      Integer;
  Sum:    Currency;
begin
  Sum := 0;
  if (DBGrid1.SelectedRows.Count>0) then
  begin
    for I := 0 to DBGrid1.SelectedRows.Count-1 do
    begin
      DBGrid1.DataSource.DataSet.GotoBookmark(Pointer(DBGrid1.SelectedRows.Items[I]));
      Sum := Sum + DBGrid1.DataSource.DataSet.FieldByName('TOTAL').AsFloat;
    end;
  end;
  Result := Sum;
end;

procedure TForm1.DBGrid1CellClick(Column: TColumn);
begin
  Edit1.Text := FloatToStr(GetSum);
end;

end.
epasquier,
it's a indeed a running totals
but for this sample, it's not from 0 to Nth row up to Nth row
it's for all the rows and it puts the value in every row
this may be a little too much, adding a complete column with the same value, i agree

if you would want running totals up to Nth row,
you would use a range to limit the aggregation
for a running totals up to the Nth row:
sum(columnX) over (order by columnX range between unbounded preceding and current row) sum_columnX,

for a running total per article per N rows
sum(columnX) over (partition by article order by article, columnX range between unbounded preceding and current row) sum_article_columnX,

i was going to pull some samples out my hat, but then this came up with google:
http://orafaq.com/node/55
i can't do it any better than that. :)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Another option is the ROLLUP function of SQL Server :D This will return an extra record or set of metadata containing the totals.
Hi Guys

All of the responses above have been great and i have spent a lot of time working through individual ideas. Ultimately there is many ways in which this can be challenged.

I did however invest in TMS Grid Pack which seemed to solve all my issues over night. The features far out way the standard DBGRID and i have managed to provide a feature rich query application for numerous employees. Trying the free alternatives is always a positive approach for me but sometimes it is worth while saving your days and weeks of trial and error and just investing a small sum of money.

I really do appreciate all your answers and I think it as been a good healthy debate amongst the experts to which approach to take.

The Grid Pack I used is available at http://www.tmssoftware.com