I use a GetColumn custom function ( FileMaker Custom Function:GetColumn ( text ; columnNumber ; columnDelimiter ) ) to separate the label from the data. For the data source I use delimited data.
So the labels end up being:
and the data is:
Setting the data source as delimited is the key though.
I ran a test with the following Query in FileMaker 15:
ExecuteSQL ( "
SELECT Sum ( \"value\" ) FROM TestTable
Group By \"Category\"
; "" ; "" )
And it worked for me. I didn't have to list \"Category\" in the first part of the query.
I didn't think of using the SUM( )
But count should have worked also--at least it shouldn't result in a ? result. It should have counted records for you.
I got valid results on the same test file using:
ExecuteSQL ( "
SELECT Count ( * ) FROM ESQLGroups
Group By Category "
; ""; "" )
So counted or summed values should be possible tools for producing the needed delimited data without having to include the actual field on which the data is being grouped.
The actual values being returned will be different unless the field being summed only has the value of 1 in all records.
I've always used a single SQL statement and parsed because I'm working with a huge data set and I'm guilty of wanting to see the data in a readable format. How is the processing speed with the two SQL statements vs a single SQL?
Seems like any gains from doing one SQL query will be lost due to having to then parse the single list into separate lists of values. And you'd have a slightly more complex solution to maintain as well.
But you'd have to run tests to be sure and I suspect that you might get different results depending on the table, indexing, the total number of records and what parsing method (There's a customLists function out there that's amazing and nonrecursive as well...) you used to parse the lists.