When using the executeSQL function with a GROUP BY I'm unable to return additional fields in Filemaker versions 11 and 12, as Filemaker has made the GROUP BY a lot stricter. Because Filemaker has made the GROUP BY syntax stricter there is no way to return additional fields unless you use a subquery in the FROM clause. An example of the work around taken from stack overflow below.
select e.empID, fname, lname, title, dept, projectIDCount
select empID, count(projectID) as projectIDCount
from employees E left join projects P on E.empID = P.projLeader
group by empID
inner join employees e on idList.empID = e.empID
After some unsuccessful experimentation it would seem that Filemaker's SQL does not support subqueries in the FROM clause.
GROUP BY worked flawlessly prior to version 11. (not including subquery)
1. Does this mean the SQL engine will be updated at a future time? We are already two Filemaker versions into a new SQL engine.
2. Is there another way to return additional fields when doing a GROUP BY without writing a lot of convoluted code?