• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 819
  • Last Modified:

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
0
TG-Steve
Asked:
TG-Steve
  • 5
  • 4
  • 3
  • +5
12 Solutions
 
epasquierCommented:
you can either create a separate request for the sum, or do it manually.
That second option is not too difficult nor would be much strain on workstation CPU, I would go for that one
0
 
TG-SteveAuthor Commented:
epasquier

if you dont mind me asking do you have an example of the 2nd option please
0
 
8080_DiverCommented:
Actuall, you don't need to do a separate request for the summation . . . at least in the sense that you don't need to execute a separate Tquery. ;-)
The attached query is kind of generic but should give you the idea.

SELECT Y.yourcolumns,
       Z.YourSum
FROM yourtable Y
INNER JOIN
(
 SELECT someofyourcolumns,
        SUM(desiredcolumn) YourSum
 FROM   yourtable
 WHERE  sameconstraintsasouterquery
 GROUPD BY someofyourcolumns
) Z
ON Z.Identifercolumn = Y.IdentifierColumn
WHERE outerqueryconstraints;

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
8080_DiverCommented:
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.
0
 
epasquierCommented:
that is not a separate query, but in terms of performance that is worse than 2 queries (time to execute, network bandwidth and memory). So what is the gain ?
0
 
8080_DiverCommented:
epasquier,
While I haven't checked the query plan, I would have to wonder about your blanket statement that it "is worse than 2 queries".  The calculations of the subquery will be performed once and then joined with the individual rows of the outer query, so, yes there is a bit of additional overhead time.  However, the trade-off is in the overhead of executing two queries separately (which is not trivial either).  
Addmittedly, it does suck a little more bandwidth and a little more memory but, in this case, I would think that, unless there are a few million rows being returned, the bandwidth and memory iesues would not be terribly significant.  If they are, then the queries could be separated after the single one  had provided proof ocf concept.  (Also, if the memory and bandwidth issues arise, I would be more concerned about the memory and bandwidth issues in general than about this one query. ;-)
Another benefit is that it involes less in the way of programming changes because it uses an existing connection, an existing TQuery object, and existing TDataSource, and the TEdit to display the calculated value can simply be joined to the single TDataSource.  
Using a second TQuery and TDataSource would not only require coding to handle their set up and opening but also coding to execute the query and, if one dioes it right, additional error handling wrapped around that TQuery.Execute.
 
0
 
Geert GruwezOracle dbaCommented:
>>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/
0
 
rfwoolfCommented:
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;
0
 
briangochnauerCommented:
Some grids are designed to sum columns automatically; personally I use DevExpress QuantumGrid
0
 
8080_DiverCommented:
rfwoolf,
Isn't that kind of doing it the hard way? ;-)  If there are very many rows, it would be quicker to execute another query.
0
 
rfwoolfCommented:
8080>
epasquier:
"you can either create a separate request for the sum, or do it manually.
That second option is not too difficult nor would be much strain on workstation CPU, I would go for that one

OP:
if you dont mind me asking do you have an example of the 2nd option please
0
 
epasquierCommented:
@rfwoolf : same as your example, but without finding the same field over and over in a string-indexed list (ByName...)

I really don't understand why I am the only one in ExEx that do not use FieldByName, and instead use the design-time created typed fields.... Delphi IDE may give them ugly names, but that is a lot better in terms of performance. And also easier to write code with them.

> Diver : Isn't that kind of doing it the hard way? ;-)  If there are very many rows, it would be quicker to execute another query.
That does not takes a modern CPU more than a few ms to run that from hundreds of lines in memory.
And I find that kind of code a lot easier than your query. But that is only my opinion, and I understand that some people that do much more SQL queries than me can think the other way.

The thing is I don't believe in putting every piece of calculation that simple on the DB side. That's how an application running on an intel core machine will spend most of its time waiting for a DB to return this and that value, and/or will end up using much more data than necessary because every data structure does not have to fit a table, in your case with one column having always the same value that is not per-se related to each row independantly but to all of them.
I prefer my applications to rely on the DB to present data the most natural way, with simple queries, and that they take their part of the job with the presentation of the data. After all, that is just a matter of display choice if we want the sum of this or that column. One just shouldn't have to change the query to add one such information.

And if I had the choice, I would use as briangochnauer said a grid that display the data and at the same time (so almost without additional workload) sums up some columns. That beats EVERY other solution in ALL aspects (except cost maybe - for the component suite)
// this function would probably be in a datamodule, with the DB objects
Function TDataModule.GetSum:Double;
begin
 Result := 0;
 qryOrderItems.First;
 While Not qryOrderItems.Eof do
  begin
// qryOrderItemsPrice is a TCurrencyField or TFloatField created
// from the IDE
    Result := Result + qryOrderItemsPrice.Value;
    qryOrderItems.next;
  end;
end;

Open in new window

0
 
epasquierCommented:
@Geert : Sorry I didn't saw your post before. You are then the father of QuatumGrid solution, which makes three of us think it's probably the best solution. But with a cost.

Now, about the DB and new Oracle capabilities : I admit that it has been a while since I've done heavy duty DB applications, and nowadays I merely do simple requests with old-style SQL statements. Of course I expect those DB have evolved much in the ~15 years from the time I have learned SQL, and I also expect that the machines on which they can run have also had a massive boost in performances. But so does the workstations, and mine is just doing nothing most of the time, so it might be a good idea to occupy the workstation CPU as well...

Now, can you explain us that SQL syntax :
select *,
  sum(columnX) over (order by columnX) sum_columnX,
  count(columnX) over (order by columnX) count_columnX,
from tableA

is that some sort of running totals, from row 0 to N, for each Nth row ?
The total sum is then the value for the last row ?
If that is so, I admit THIS is a good thing, and most certainly useful on the DB side.
0
 
senadCommented:
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.
0
 
Geert GruwezOracle dbaCommented:
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. :)
0
 
briangochnauerCommented:
@Geert_Gruwez:  That is a Oracle specific feature, he said he was using SQL Server (MS)
Generally, Yes you can run a second query but integrating into a single grid is exceptionally difficult, fraught with gotchas.

Get the grid to do it for you; is the only reasonable solution.
0
 
Ephraim WangoyaCommented:
Just a few comments

If the rows being returned are not in the hundreds of thousands, there is really no harm in totalling them staright from the dataset assuming its not unidirectional. What is a few hundred lines to a 3Ghz cpu?

procedure Form1.SumTotals;
var
  Field: TField;
  Total: Double;
begin
  //no need for bookmarking anything, you just loaded the data

  Field := ADOQuery.FieldByName('YOURCOSTFIELDNAME');
  ADOQuery.DisableControls;
  try
    ADOQuery.First;
    while not ADOQuery.Eof do
    begin
       Total := Total + Field.AsFloat;
       ADOQuery.Next;
    end;
    Label1.Caption := FloatToStr(Total );
  finally
    ADOQuery.First;
    ADOQuery.EnableControls;
  end;
end;

If you have a lot of data, use a thread (my preference)
  TOnCalculateTotal = procedure(Sender: TObject; const ATotal: Double) of Object;

  TTotalsThread = class(TThread)
  private
    FConnection: TADOConnection;
    FSQL: string;
    FTotal: Double;
    FOnCalculateTotal: TOnCalculateTotal;
    procedure DoUpdateTotal;
  public
    constructor Create(AConnection: TADOConnection; const ASQL: string;
      ACalculateTotal: TOnCalculateTotal);
    procedure Execute; override;
  end;

{ TTotalsThread }

constructor TTotalsThread.Create(AConnection: TADOConnection;
  const ASQL: string; ACalculateTotal: TOnCalculateTotal);
begin
  FConnection := AConnection;
  FSQL := ASQL;
  FOnCalculateTotal := ACalculateTotal;
  FreeOnTerminate := True;
  inherited Create(False);
end;

procedure TTotalsThread.DoUpdateTotal;
begin
  if Assigned(FOnCalculateTotal) then
    FOnCalculateTotal(Self, FTotal);
end;

procedure TTotalsThread.Execute;
var
  Query: TADOQuery;
begin
  FTotal := 0;
  Query := TADOQuery.Create(nil);
  try
    Query.Connection := FConnection;
    Query.SQL.Add(FSQL);
    Query.Active := True;
    if not Query.IsEmpty then
    begin
      FTotal := Query.FieldByName('TOTAL').AsFloat;
      Synchronize(DoUpdateTotal);
    end;
    Query.Active := False;
  finally
    FreeAndNil(Query);
  end;
end;

in your form you would have the procedure
procedure TForm2.CalculatedTotal(Sender: TObject; const ATotal: Double);
begin
  Label1.Caption := FloatToStr(ATotal);
end;

After the data is loaded to your grid, create the thread
procedure TForm1.GetTotal;
var
  SQLStr: string;
  MyThread: TTotalsThread;
begin
  SQLStr := 'Select sum(totalfieldname) from table1';
  //add your filter
  //where date > '01/01/2010'
  MyThread := TTotalsThread.Create(ADOConnection1, SQLStr, CalculatedTotal);
end;


0
 
8080_DiverCommented:
What is a few hundred lines to a 3Ghz cpu?
As a passing observation, if it takes longer to process the lines RBAR, then, even though you don't necessarily notice the difference, the difference is that you have wasted resources (i.e. cpu cycles and time) unnecessarily.  If you get into a more heavily loaded situation, you may discover that what was once barely noticeable becomes very noticeable.
However, that is from a set-based, time and resource sensitive point of view and not from the point of view of someone who has been spoiled by having, essentially, all the time and resources in the world. ;-)  I think more in set-based operations and I come from a time when CPU Cycles were a precious commodity . . . so I still tend to try to write my code as tightly as possible. ;-)
0
 
rfwoolfCommented:
Another option is the ROLLUP function of SQL Server :D This will return an extra record or set of metadata containing the totals.
0
 
TG-SteveAuthor Commented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 3
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now