Sub-summarizing a List

[Rich]

Greets, all:

I know FSQL isn't for creating documents, but tables, but this should be do-able: I have a single table (OJI) where I'm only concerned with two out of its many fields, Injury_Root_Cause and zz_RecordID. What I want to do is sub-summarize by Injury_Root_Cause and list each's respective (unique) zz_RecordID's under each heading--sorted in ascending order--horizontally listed and each zz_RecordID separated by a comma.

So far, I have some of the calculation working:

ExecuteSQL ( "

SELECT zz_RecordID FROM OJI

WHERE Injury_Root_Cause = 'Carelessness'

ORDER BY zz_RecordID

" ; "" ; ", " )

That works great for listing the zz_RecordIDs for one value, Carelessness, but it doesn't list the Injury_Root_Cause's value.

 

I stumbled on one possible way of doing this by gutting an example on the 'net, but I got lost in the syntax so it doesn't work:

ExecuteSQL ( "

SELECT * FROM    

   (    SELECT Injury_Root_Cause,zz_RecordID    

   FROM OJI GROUP BY Injury_Root_Cause,zz_RecordID   )

   ORDER BY Injury_Root_Cause,zz_RecordID

" ; "" ; ", " )

Soooooo, if you see where I need to tweak the calculation I'd greatly appreciate it.

Cheers!

[comment]

1 hour ago, Rich said:

I stumbled on one possible way of doing this by gutting an example on the 'net,

Can you post a link?

[Rich]

Sorry, I should've included it earlier: https://stackoverflow.com/questions/25042819/whats-the-sql-equivalent-of-a-filemaker-subsummary

I used the second example; I excluded ROLLUP since I don't think it's supported in FSQL.

[comment]

The second answer there shows a result where each title is a row, and the author's name is repeated. IIUC, you want a row for each distinct author, with a list of that author's titles following in the same row. The first answer shows such result - but GROUP_CONCAT() is not supported by FMP's Execute SQL() - and I am not sure there is another way to get such result using SQL.

However, it would be easy to get it using a looping script. And it could be fast too, if you add a summary field listing the titles and use the Fast Summaries method by Mikhail Edoshin.

It might be even easier if you had a table of Authors.

 

 

Edited August 29, 2019 by comment

[Rich]

Thanks--I'll take a look at Edoshin's Fast Summaries.

 

Yeah, I originally thought about using a looping script but I got hung up trying to create the comma-separated field of RecordIDs; it just occurred to me that I can re-use/overwrite a dedicated/new text field with each loop of the script, adding ", " & RecordID to the calc.

Cheers,

Rich

[comment]

1 minute ago, Rich said:

I can re-use/overwrite a dedicated/new text field with each loop of the script,

That's not necessary. Use a variable, and add a comma and a value to the variable at each iteration. When you get to a new cause, add the accumulated line to the result (another variable), and initialize the line variable by setting it to the current cause. That's if you're looping over each record in the found set; the Fast Summaries method allows you to set the result with a complete line at each iteration, and jump directly to the next cause.

 

[Rich]

Cool. Thanks again!

[comment]

Actually, it can be even simpler. Have a look at the attached demo - it shows both methods.

SummarizeLists.fmp12

[Rich]

Briliant!