[SOLVED] Summarizing a spreadsheet with SQL

[maurobio]

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][+][-]

1. 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][+][-]

1. Plot    avg(N)  avg(P)  avg(K) ...
2. A1      18.0    52.0    186.5
3. A2      23.75   40.25   165.0
4. A3      26.0    53.0    230.25
5. A4      26.0    30.5    83.25
6. A5      21.5    47.75   147.25
7. A6      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]

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]

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]

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]

Dear ALL,

Unbelievable! It turned out that the problem was in the line below:

Code: Pascal  [Select][+][-]

1. 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.