ORDER BY does not specify a column from the SELECT directly. I am not sure if FM support order by ordinal.
Selecting a COUNT and a column as you use might need to return the same numbers rows to work.
Start with the simple select and add things for testing.
Does this work:
SELECT COUNT (IncidentSummary_Software), IncidentSummary_Software
FROM Service_Management"; ""; "")
That does work but it doesn't look right. I had put the cout first to make look neat but I really want it to look like my example
You have three things in your example, but only two in the SELECT. How does it not look right?
I never got to the 3rd item. Just added that in before submitting my question
2 of 2 people found this helpful
I know you asked for a non-ExecuteSQL solution, but I'm a fan of correlated subqueries for this kind of thing. Something like this should be close:
SELECT IncidentSummary_Software, COUNT (IncidentSummary_Software), '' || round(
WHERE Month (Time_Created) = ? and IncidentSummary_Software = a.IncidentSummary_Software) / (
WHERE Month (Time_Created) = ? ),2) * 100 || '%'
FROM Service_Management a
WHERE Month (Time_Created)=?
GROUP BY IncidentSummary_Software
ORDER BY 2 DESC
FETCH FIRST 10 ROWS ONLY"
; Char(9) ; ""; Month (Get (CurrentDate)); Month (Get (CurrentDate)); Month (Get (CurrentDate)))
A couple of things to consider:
- This is calculating the percentage based on all of the records for the month, not just the total of the top 10.
- If you have more than one year of records, you'll want to add the year to the WHERE clauses.
- If it's a large table and performance is an issue, consider restructing the WHERE clauses to use the BETWEEN operator and pass the first and last day of the month as arguments. That should perform better since FileMaker wouldn't have to calculate the month (and year) for the date in each record in the table.
Let me give this a go
There are non-SQL ways to get this report, but it wouldn't use calculation fields. You'd use summary fields for both the count and % (fraction of total).
You'd need a script to narrow the results to top ten, but it would not be a complex script.
Do tell please sir. EXECUTESQL just doesn't look as pleasing.
I agree with Phil. I would use a script and likely use eSQL in the script for a few things.
I'm just learning sql from the group so I am a lot green on this stuff. Never been good at reading code. I learn from a lot of questions as it pertains to what I'm working on.
1 of 1 people found this helpful
Well you "agree" with me but then go on to recomend eSQL.
Hey, there's many ways to skin this cat and I'm not saying one is better than the other, just noting that this is rather odd "agreement"!
Since I was asked to spell out the details. A summary field can be a 'count of' field to count records. that's field 1. You have field 2. A summary field that computes a "fraction of total" can be formatted to display as a percent works as field 3.
The totally non-SQL method that I had in mind works like this:
Find your records, except you don't yet try to figure "top ten" as that requires computing some sub totals.
Sort to group your records. In this case that would be sorting by IncidentSummary_Software. Then you sort again. There's a sort option that allows you to "re-order based on summary field" where you select the counting summary field and descending order to re-order them from largest subtotal to smaller.
On a layout, you can use a sub summary part "when sorted by" IncidentSummary_Software to show the incident category, count and %. You can delete the body so that you only get one row of data to a group.
The last step needs a script. You use a script to loop through the records until you get to the first record not part of the top ten values and then use Omit Multiple Records to omit the remaining records.
The re-ordering step can be slow with large found sets, but it does work.
The agreement is that there are easier ways than trying to squeeze it all into one SQL query.
Easier to grasp. Easier to debug. Easier to read. I am not against the non eSQL method. I just think that using simpler queries in parts of the script would be effective and it is likely what I would choose to do.
That being said, knowing how to do it without eSQL is a valuable thing.
2 of 2 people found this helpful
SQL can get quite complicated. A "Correlated Sub-query" is when you use fields in the outer query referenced (used) in an inner query. It's a bit advanced. SQL is amazing since it's "declarative" -- meaning you don't have to "code it". You say "what" rather than "how" and the SQL engine figures out how to do it.
I would recommend mastering SQL, even if it's a bit painful, as it's used in every database out there. Fortunately, there are so many excellent free resources for that!
There are sometimes proprietary additions made to SQL (like Oracle's "Minus" clause), but I believe basic SQL, including correlated sub-queries should be in any database developer's wheelhouse.
Thanks for your help Phil. However I'm having issues now with the final part:
"The last step needs a script. You use a script to loop through the records until you get to the first record not part of the top ten values and then use Omit Multiple Records to omit the remaining records."