[SOLVED] Summarizing a spreadsheet with SQL lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- maurobio · 2021-11-04 14:38 Dear ALL, I have a simple spreadsheet in a FPSpreadsheet component connected to a DBGrid, in which the columns are measurements of soil properties in a number of field plots. I want to summarize these data by computing the average values of each data column and grouping them by plot, using a SQL query as follows: 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 SELECT Plot, AVG(N), AVG(P), AVG(K) ... FROM Variables GROUP BY Plot so that I would get a summary table as follows: 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 Plot    avg(N)  avg(P)  avg(K) ...A1      18.0    52.0    186.5A2      23.75 40.25   165.0A3      26.0    53.0    230.25A4      26.0    30.5    83.25A5      21.5    47.75   147.25A6      16.75   43.75   162.5 For this I use a ZMSQL component (https://wiki.freepascal.org/ZMSQL), which should process the SQL query for displaying in another DBGrid. It should be quite simple, and I have already used the ZMLSQ component to perform queries on spreadsheets connected to FPSpreadsheet component without any trouble. However, now I keep getting the message "Operation cannot be performed on an inactive dataset", even if I am making sure that the dataset is open and active. I may be getting dumb over time! Could someone give me a hand to solve this? My sample code is attached. Thanks in advance! With best wishes, ---------------------------------------------------------------------- winni · 2021-11-04 16:43 Quote from: maurobio on November 04, 2021, 03:38:57 pm However, now I keep getting the message "Operation cannot be performed on an inactive dataset", even if I am making sure that the dataset is open and active. Hi! Is the DataSet of the second DBGrid open???? Winni ---------------------------------------------------------------------- wp · 2021-11-04 17:03 Didn't we have this issue with ZMSQLDataset already earlier? Since I cannot help you with this I am sending a solution based on fpspreadsheet alone. ---------------------------------------------------------------------- maurobio · 2021-11-04 19:14 Hi, @winni, @wp! Thank you for your answers. Yes, @wp, we did have a similar issue wit TZMSQL before, and it was solved, so I do not know exactly why is going wrong now. But my sample code is messy and I will have to check it entirely (including a check of all the connections as @winni pointed out). @wp, your solution is beautiful, with a plot! Bit I need to stick to SQL queries because many other parts in the logic my application use and require them. With best wishes, ---------------------------------------------------------------------- maurobio · 2021-11-04 22:13 Dear ALL, Unbelievable! It turned out that the problem was in the line below: 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 SqlStr += ' FROM Variables [b]GROUPY[/b] BY ' + SAMPLE; There was a simple typo in the "GROUP BY" clause! Unfortunately, the error messages from ZMSQL are not very clear (if they are clear at all). My fixed sample code is attached. I apologize for bothering you because of a stupid typo.  With best wishes.