Recent

Author Topic: How to SUM a field in the SQLQuery dataset to a variable  (Read 719 times)

erictan

  • Jr. Member
  • **
  • Posts: 52
How to SUM a field in the SQLQuery dataset to a variable
« on: July 07, 2020, 03:12:18 pm »
Instead of using "while not SQLQuery.Eof do" loop to SUM a numeric field to a variable in a dataset, can I use a SUM function on the dataset so that the cursor in the dataset is not moved to the end record.
All advise are greatly appreciated.

mig-31

  • Sr. Member
  • ****
  • Posts: 278
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #1 on: July 07, 2020, 03:46:01 pm »
Hi,

you should use SUM SQL function do that in the SQL Query (in case if you don't need the sum of whole collumn use where clause) and pass the result to the variable
Code: Pascal  [Select][+][-]
  1.    SQLQuery.SQL.Text = 'SUM(<FIELD > FROM <TABLE>)';
  2.    var1 = SQLQuery.Fields.FieldByName('SUM').AsInteger;
  3.  
Lazarus 2.0.6 - CentOS 7.x, Mageia 7.1

erictan

  • Jr. Member
  • **
  • Posts: 52
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #2 on: July 07, 2020, 04:47:55 pm »
Thank you mig31 for your reply, I presume this SQL statement query the database table in the server.

Does this statement also apply to the local SQLQuery dataset that I have already extracted from the database server.

I am basically working on the local Query dataset that was extracted from the database server.

erictan

  • Jr. Member
  • **
  • Posts: 52
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #3 on: July 07, 2020, 04:53:55 pm »
After extracting the dataset from the database server, I am using a "While DO" Loop to do the SUM of the "amount" field in the SQLQuery Dataset like.

var
  lnTotalamt : real;
begin
  SQLQuery.First;
  while not SQLQuery.Eof do
  begin
    lnTotalamt := lnTotalamt + SQLQuery.FieldByName('amount').AsFloat;
    SQLQuery.Next;
  end;
end;

edtTotalamt.Text := FloatToStr(lnTotalamt);

The above will move the cursor to the last record which I am trying to avoid.


« Last Edit: July 07, 2020, 04:56:59 pm by erictan »

dsiders

  • Sr. Member
  • ****
  • Posts: 333
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #4 on: July 07, 2020, 05:05:17 pm »
After extracting the dataset from the database server, I am using a "While DO" Loop to do the SUM of the "amount" field in the SQLQuery Dataset like.

var
  lnTotalamt : real;
begin
  SQLQuery.First;
  while not SQLQuery.Eof do
  begin
    lnTotalamt := lnTotalamt + SQLQuery.FieldByName('amount').AsFloat;
    SQLQuery.Next;
  end;
end;

edtTotalamt.Text := FloatToStr(lnTotalamt);

The above will move the cursor to the last record which I am trying to avoid.

So call SQLQuery.First when the loop is done.
Lazarus 2.1 (SVN) / FPC 3.0.4 / FPC 3.2.0 / x86-win64 / Windows 8.1

erictan

  • Jr. Member
  • **
  • Posts: 52
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #5 on: July 07, 2020, 05:13:55 pm »
I wanted to retain the cursor at the current position as I am using the DBGrid as a Data Entry form.

erictan

  • Jr. Member
  • **
  • Posts: 52
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #6 on: July 07, 2020, 05:25:52 pm »
I could use the Locate function to move the cursor position back but I think there could be a more efficient way of doing it.
I thought mig-31 solution looks good if it can apply or modified for local dataset.

TRon

  • Sr. Member
  • ****
  • Posts: 407
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #7 on: July 07, 2020, 06:17:03 pm »
You could perhaps make use of a bookmark https://www.freepascal.org/docs-html/fcl/db/tdataset.bookmark.html

But, I  also don't understand what would be wrong with using a second query ?

GetMem

  • Hero Member
  • *****
  • Posts: 3752
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #8 on: July 07, 2020, 08:36:38 pm »
@erictan
Quote
I could use the Locate function to move the cursor position back but I think there could be a more efficient way of doing it.
Your solution is perfectly fine, just disable the controls to prevent flickering and speed up the loop, like this:
Code: Pascal  [Select][+][-]
  1. var
  2.   lnTotalamt : real;
  3.   ID: Integer;
  4. begin
  5.   lnTotalamt := 0;
  6.   ID := SQLQuery.FieldByName('ID').AsInteger;
  7.   SQLQuery.DisableControls;
  8.   try
  9.     SQLQuery.First;
  10.     while not SQLQuery.Eof do
  11.     begin
  12.        lnTotalamt := lnTotalamt + SQLQuery.FieldByName('amount').AsFloat;
  13.        SQLQuery.Next;
  14.     end;
  15.     SQLQuery.Locate('ID', ID, []);
  16.   finally
  17.     SQLQuery.EnableControls;
  18.   end

PS: In my example ID is the primary key, the same applies to other fields assuming they are unique.

erictan

  • Jr. Member
  • **
  • Posts: 52
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #9 on: July 08, 2020, 01:33:36 am »
Thank you GetMem, just tried your sample and it "works like a charm", this is just what I wanted.

In my past Visual Foxpro experience, I would just Select the Dataset into a temp Cursor and SUM the amount in the Cursor. A 3-liner statement solution. I was wondering how to do that in Lazarus.

Unfortunately the demise of Visual Foxpro forced me to migrate to another platform and Lazarus/Firebird seems to be a good alternative platform and I am enjoying the long and tedious relearning process.

Thank you again, GetMem, TRon, dsiders and mig-31 for your help, greatly appreciate it.
Be Safe from Covic-19.

erictan

  • Jr. Member
  • **
  • Posts: 52
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #10 on: July 08, 2020, 04:43:34 am »
On further testing, I realize that in the DBGrid column, I am not able to mouse-click in-between letters in the column text to insert or delete any characters  as it is not in Edit mode even though I have tried setting SQLQuery.Edit.

I have to highlight the whole text in the DBGrid column before I can do any editing.
This only happen to DBGrid columns with text strings but columns with non-text string like Float and Integer do not have this issue.

I can confirm that this issue also happens to DBEdit besides DBGrid component.

It is counter-productive to have to highlight the whole text and re-type the whole text just to amend a few characters in the column text box.

I hope there are some settings that I can set to allow in-between character editing.

Appreciate any further help and suggestions.
« Last Edit: July 08, 2020, 04:45:38 am by erictan »

GetMem

  • Hero Member
  • *****
  • Posts: 3752
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #11 on: July 08, 2020, 07:10:31 am »
Set DBGrid->Options->dgAlwaysShowEditor and dgEditing to true. When you click a cell, the grid goes into edit mode and the current cell text is selected. If you doubleclick a  cell you can directly insert a character(in-between character editing). In my opinion this is normal grid behaviour.

« Last Edit: July 08, 2020, 07:15:05 am by GetMem »

erictan

  • Jr. Member
  • **
  • Posts: 52
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #12 on: July 08, 2020, 07:39:59 am »
Thanks again GetMem, you are right about the Lazarus DBGrid behaviour which also apply to DBEdit.

Guess I will have to get used to this new way of working with Lazarus DBGrid and DBEdit data-aware component..

delphi2pk

  • Newbie
  • Posts: 3
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #13 on: July 08, 2020, 09:10:15 am »
Use a bookmark for this:
Code: Pascal  [Select][+][-]
  1. var
  2.   tAmnt : real;
  3.   bm : TBookMark;
  4. begin
  5.   tAmnt := 0;
  6.   bm := qry.GetBookMark;
  7.   qry.DisableControls;
  8.   try
  9.     qry.First;
  10.     while not qry.Eof do
  11.     begin
  12.        tAmnt := tAmnt + qry.FieldByName('amount').AsFloat;
  13.        qry.Next;
  14.     end;
  15.   finally
  16.     qry.GotoBookMark(bm);
  17.     qry.FreeBookMark(bm);
  18.     qry.EnableControls;
  19.   end;
  20. end;
  21.  
« Last Edit: July 08, 2020, 11:51:10 am by delphi2pk »

rvk

  • Hero Member
  • *****
  • Posts: 4227
Re: How to SUM a field in the SQLQuery dataset to a variable
« Reply #14 on: July 08, 2020, 11:45:14 am »
I'm not sure how it's in the TDBGrid from Lazarus but I always noticed in Delphi that with this method, even with DisableControls, the current TDBGrid position was shifted to the middle. So the cursor for the dataset was correct but all the records jumped in the grid.

My solution was to also save the grid position and do a Move(+) and Move(-) to make sure the grid position was restored too.

 

TinyPortal © 2005-2018