Calculating the average salary for employees of the same title

Question

Hi everyone,I have two tables in a HR app, one called Team Members and the other Compensation.In Team Members I have the Team Member name and their TitleIn Compensation I have a record for each salary so I can keep track of their history.In Team Members I am summarizing the maximum salary to get the employees current salary.In Compensation I am looking up the name and title of the Team MemberWhat I want to do is calculate the average salary per title.&nbsp; I am fairly certain I need to do a formula query to do this but I am trying to dynamically determine how many records and calculate it.The logic around this in english is:Get the records of all of the team members with the same titleSum all of their current salariesDivide that by the number of recordsThat should return the average salary.Can anyone help me get started on how I would go about doing that?------------------------------Ivan Weiss------------------------------

ivanweiss · Answer

I actually solved my own problem so figured I would post the results. I dont know why but I find formula queries so intimidating. But once I wrote out what I was trying to do in English here it got a lot more clear for my query so I was able to do it:

//Get all of the Team Member Records in which the title is the same
//Summarize all of the Current Salaries for these records
//Divide the sum of salaries by the number of records
//Return the Average Team Salary

//Field ID's
//[Title] = 28
//[Current Salary] = 33

//QUERY = Give me the Team Member Records in which the title is the same
//QUERY Exclude any records where the salary is 0
var text QUERYONE = "{28.EX.'" & [Title] & "'} AND {33.GT.0}";

//Determine the number of records in the above query
var number NumberTeamMembers = Size(GetRecords($QUERYONE));

//Determine the total salary of employees in the above query
var number TotalSalary = SumValues(GetRecords($QUERYONE),33);

//Return the Total Salary / Number of Team Members with the same title
$TotalSalary / $NumberTeamMembers

------------------------------
Ivan Weiss
------------------------------

Forum Discussion

Calculating the average salary for employees of the same title