Rich S Posted April 30 Share Posted April 30 Greets, all: I have a calculation that works as is: ExecuteSQL ( " SELECT Department__lxt, COUNT ( * ) FROM ESU7_STAFF WHERE Status__lxt = 'Active' GROUP BY Department__lxt UNION ALL SELECT '¶Total' Department__lxt, COUNT (Department__lxt) FROM ESU7_STAFF WHERE Status__lxt = 'Active' " ; Char (9) ; "" ) ...but the output of departments is unsorted. (See below.) I'd like to have the departments in ascending order, but I just can't get the syntax right using ORDER BY; how/where should it go? As always, TIA for your help! Link to comment Share on other sites More sharing options...
comment Posted April 30 Share Posted April 30 Before starting: Do you really have a field with the department's name in the staff table? I would expect to see a parent table of departments, with the staff holding only a DepartmentID value. Link to comment Share on other sites More sharing options...
Rich S Posted April 30 Author Share Posted April 30 (edited) Indeed, there is a separate Department table, so in the Staff table there's a field that holds the foreign key and a separate field that holds the name (Department__lxt) as well. (Redundant, I know.) Edited April 30 by Rich S Link to comment Share on other sites More sharing options...
comment Posted April 30 Share Posted April 30 (edited) The ORDER BY clause should come at the very end. But there is a problem here: The sort will be applied to the entire result - including the "Total" line produced by the 2nd SELECT statement. If it starts with a ¶, it will be sorted to the top. If it starts with a "T", it will come before "Vision". You could try and prepend a control character to force it to be the last - for example, this seems to work in my test: ExecuteSQL ( "SELECT Department, COUNT (*) FROM Staff WHERE Status = 'Active' GROUP BY Department UNION SELECT CHR(127) + '¶Total', COUNT (*) FROM Staff WHERE Status = 'Active' ORDER BY 1" ; Char (9) ; "" ) However, I would rather make 2 separate calls to the ExecuteSQL() function and combine the result using the FMP calculation engine. And of course, if you can produce the same result using a FMP native report, that's always preferable. Edited April 30 by comment 1 Link to comment Share on other sites More sharing options...
Rich S Posted April 30 Author Share Posted April 30 Beauty! Thanks for the tips, too. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now