AnsweredAssumed Answered

executeSQL select percent of group by

Question asked by scott777 on Jul 16, 2014
Latest reply on Jul 16, 2014 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

      

      

Outcomes