How to SUM a decimal field in the SQLQuery dataset lazarus / 13-General ---------------------------------------------------------------------- erictan · 2020-07-21 01:34 In a Lazarus DBGrid form, I have extracted a dataset from the table called invoiceDetails using the statement SQLQuery.SQL.Text := 'select * from invoiceDetails WHERE invoiceno = :mInvoiceno'; It extracted 5 records correctly and I want to SUM the total of the 'amount' decimal field in the SQLQuery dataset to a variable called 'lnTotalamt'. How can I do that ?. I have google and tried many SQL Select SUM statements without success on the Dataset. Any help is greatly appreciated. ---------------------------------------------------------------------- balazsszekely · 2020-07-21 04:25 1. Use a calculated filed 2. Second query with "select sum(amount) as InTotalamt from invoiceDetails where invoiceno = :mInvoiceno" 3. Stored procedure ---------------------------------------------------------------------- erictan · 2020-07-21 06:41 Thank you GetMem for your reply. As for your option-2, I only want to get the SUM from the dataset and not the backend table InvoiceDetails. I am using the DBGrid for data-entry where I insert many records and it will SUM the amount of all the records from the dataset into a vaiable which is shown in a text-box below the DBGrid. Once all the records are inserted in the dataset I will then click SAVE button to update the dataset to the backend table InvoiceDetails. Can you explain more on your option-1 and option-3 Thanks again. ---------------------------------------------------------------------- rvk · 2020-07-21 11:01 Quote from: erictan on July 21, 2020, 08:41:35 amAs for your option-2, I only want to get the SUM from the dataset and not the backend table InvoiceDetails. You will, effectively, get the SUM for the dataset. You see that there is also the WEHRE clause in the SUM SQL. If you really really only want to cound the SUM in the dataset (then it's not SUM anymore but just sum, lowercase because it's not SQL) you can loop through the dataset and count all records. It's then best to do BeginUpdate and EndUpdate on the dataset so that any connected DBGrid doesn't travel with it. Saving and restoring a bookmark will get you back at the same position as before. QuoteOnce all the records are inserted in the dataset I will then click SAVE button to update the dataset to the backend table InvoiceDetails.You mean you already done inserting but those are not 'saved'? How is that possible? Are you talking about a transaction? You can execute the SUM SQL in the same transaction as the not yet committed inserts. ---------------------------------------------------------------------- erictan · 2020-07-21 15:52 Thanks rvk, Can you give me a sample on how to use the sum on a dataset as I am working on the dataset before posting to the databaase table. I can edit, insert and delete many record in the dataset and when ready I click the save button to commit the dataset via transaction to the database. I basically want to do editing on the dataset including sum of all the amounts in the dataset records and post to database when ready. ---------------------------------------------------------------------- rvk · 2020-07-21 16:17 Quote from: erictan on July 21, 2020, 05:52:54 pmI can edit, insert and delete many record in the dataset and when ready I click the save button to commit the dataset via transaction to the database. Like I said... when you post, the changes should go to the database (even if you do a new insert the ApplyUpdate is done automatically I think). So you can just do the SELECT SUM() FROM TABLE but making sure the transaction of the SQLQuery is the same as your insert query. Something like this (not tested): Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i with TSQLQuery.Create(nil) dobegin  try    DataBase := your_db_connection;    Transaction := your_other_sql_transaction; // make sure this is correct    SQL := 'select sum(amount) as MY_SUM as InTotalamt from invoiceDetails where invoiceno = :mInvoiceno';    Open;    Sum := FieldByName('MY_SUM').asFloat;  finally    Free;  end;end; Quote from: erictan on July 21, 2020, 05:52:54 pmCan you give me a sample on how to use the sum on a dataset as I am working on the dataset before posting to the databaase table. there was a topic about this not long ago but I can't find it. But something like this will traverse the dataset and count your field-values as a sum. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  B: TBookmarkStr;  Sum: Extended;begin  B := SQLQuery1.Bookmark;  SQLQuery1.DisableControls;  try    SQLQuery1.First;    while not SQLQuery1.EOF do    begin      Sum := Sum + SQLQuery1.FieldByName('what_field').asFloat;      SQLQuery1.Next;    end;  finally    SQLQuery1.EnableControls;    SQLQuery1.Bookmark := B;    // do something with Sum  end;end; ---------------------------------------------------------------------- erictan · 2020-07-22 01:46 Thanks rvk, I will try both your suggestions below again. I try not to do a 'select from table' to get the sum each time I edit, add or delete a record in the dataset as this might increase the delay and network traffic to the server. I would prefer to do all the edit, add and delete the records in the local dataset, check the total amount and then post and commit the local dataset back to the table one time. I am new to dataset and find it quite restrictive in terms of manipulating the records within the dataset using SQL commands  unlike using a local cursor or temporary table. Are we able to duplicate the local dataset to a cursor or temporary table, do all the record manipulations using SQL and then copy the local table back to the dataset to be posted back to the backend table. I like the DBGrid data-aware feature which makes transaction based posting to the table very easy but find the dataset record manipulation quite restrictive. As for the 2nd solution of using 'while not SQLQuery1.EOF do', I am currently using this method but am looking for alternative to shorten the coding to one line, similar to using select(sum) like in SQL. Appreciate any further suggestions. ---------------------------------------------------------------------- rvk · 2020-07-22 13:44 Quote from: erictan on July 22, 2020, 03:46:13 amI try not to do a 'select from table' to get the sum each time I edit, add or delete a record in the dataset as this might increase the delay and network traffic to the server.When doing a Post, the data is already transferred (depending on the database). So an extra SELECT SUM is hardly any traffic at all compared to that. Quote from: erictan on July 22, 2020, 03:46:13 amI would prefer to do all the edit, add and delete the records in the local dataset, check the total amount and then post and commit the local dataset back to the table one time. I am new to dataset and find it quite restrictive in terms of manipulating the records within the dataset using SQL commands  unlike using a local cursor or temporary table. Exactly that's when the concept of transaction comes in handy. You can post, apply, delete, insert anything you want within a transaction (make sure the transaction stays open). At the moment you roll back that transaction, EVERTHING you have done is erased automatically. So there is no need for local editing (the SAVE button becomes just a Transaction.Commit and CANCEL button a Transaction.Rollback). The only reason you would want local is if you are on an extremely slow internet connection. But in that case, direct database access is really not advisable (due to a number of reasons). In that case you should look into something like REST (maybe something like kbmMW). Quote from: erictan on July 22, 2020, 03:46:13 amAre we able to duplicate the local dataset to a cursor or temporary table, do all the record manipulations using SQL and then copy the local table back to the dataset to be posted back to the backend table. You would need to copy all records to your own local (memory)dataset and work on it there. Then, afterwards, copy it all back. There is no easy offline method for this as far as I know. Quote from: erictan on July 22, 2020, 03:46:13 amAs for the 2nd solution of using 'while not SQLQuery1.EOF do', I am currently using this method but am looking for alternative to shorten the coding to one line, similar to using select(sum) like in SQL. No, there is no shorter code to count fields in a given dataset (other than the SQL way). ---------------------------------------------------------------------- erictan · 2020-07-22 13:49 Thanks again rvk for your help.