4 Replies Latest reply on Jul 16, 2014 4:54 PM by scott777

    executeSQL select percent of group by

    scott777

      Title

      executeSQL select percent of group by

      Post

           Hi,  Sorry if this answer is posted in the forums, but I could not find anything.  I am trying to use executeSQL return a list of distinct name, count, and percent.  I am using the FMServer_Sample file that comes with FM Server.  I changed the name of the column to EmployeeName so it is easier to deal with but basically there is a table in that file called:  Tasks

      Table: Tasks

      EmployeeName

           Ann Johnson

           Ann Johnson
           Frank Rankin
           Frank Rankin
           Leslie Jones
           Max Powers
           Penelope Pope
           Robert Martin
           Robert Martin
           Ryan Carter
           Tim Thomson
           Tim Thomson
           Tim Thomson
           Tim Thomson
           Tim Thomson
           Tim Thomson
           Tim Thomson
            
           The desired result would be disregard the last percent column fractions:
            
      EmployeeName theTotal PercTotal
                Ann Johnson 2.0 11.764705882352942
                Frank Rankin 2.0 11.764705882352942
                Leslie Jones 1.0 11.764705882352942
                Max Powers 1.0 11.764705882352942
                Penelope Pope 1.0 11.764705882352942
                Robert Martin 2.0 11.764705882352942
                Ryan Carter 1.0 11.764705882352942
                Tim Thomson 7.0 11.764705882352942
                 
                The SQL I am using is:
                 
      Select 
                      
      EmployeeName,
      Count(*) as theTotal,
      Count(*)/(Select count(*) from Tasks) as PercTotal
                      
      from Tasks
                      
      GROUP BY EmployeeName
                 

           This SQL errors with the ?.  When I run the SQL in NetBeans as a JDBC client to the file the error is: FQL0005/(4:9): Expressions involving aggregations are not supported.

           Interestingly, when I replace the second Count(*) call with a hardcoded number such as 200:

            

            

           Select 
           EmployeeName,
           Count(*) as theTotal,
           200/(Select count(*) from Tasks) as PercTotal
            
           from Tasks
            
           GROUP BY EmployeeName
            
           The query runs and gives the output I pasted above where every percent is obviously the same 11.7...
            
           Is this particular to FMSQL? or am I doing something wrong because I am using the Group By.  Any help would be greatly appreciated.  Thank You