Solved

Delphi Tquery data handling

Posted on 2010-09-09
20
800 Views
Last Modified: 2013-11-23
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
Comment
Question by:TG-Steve
  • 5
  • 4
  • 3
  • +5
20 Comments
 
LVL 25

Assisted Solution

by:epasquier
epasquier earned 83 total points
ID: 33639100
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
 

Author Comment

by:TG-Steve
ID: 33639308
epasquier

if you dont mind me asking do you have an example of the 2nd option please
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 83 total points
ID: 33639571
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33639589
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
 
LVL 25

Accepted Solution

by:
epasquier earned 83 total points
ID: 33640242
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
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 83 total points
ID: 33640591
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
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 33641525
>>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
 
LVL 13

Expert Comment

by:rfwoolf
ID: 33644774
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
 
LVL 5

Assisted Solution

by:briangochnauer
briangochnauer earned 42 total points
ID: 33645723
Some grids are designed to sum columns automatically; personally I use DevExpress QuantumGrid
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 83 total points
ID: 33646212
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 13

Assisted Solution

by:rfwoolf
rfwoolf earned 21 total points
ID: 33646240
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
 
LVL 25

Assisted Solution

by:epasquier
epasquier earned 83 total points
ID: 33646698
@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
 
LVL 25

Assisted Solution

by:epasquier
epasquier earned 83 total points
ID: 33646846
@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
 
LVL 22

Expert Comment

by:senad
ID: 33655332
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
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 33680237
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
 
LVL 5

Assisted Solution

by:briangochnauer
briangochnauer earned 42 total points
ID: 33681443
@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
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 21 total points
ID: 33930796
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
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 83 total points
ID: 33931238
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
 
LVL 13

Expert Comment

by:rfwoolf
ID: 33947366
Another option is the ROLLUP function of SQL Server :D This will return an extra record or set of metadata containing the totals.
0
 

Author Comment

by:TG-Steve
ID: 33949122
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now