How to get an average out of a data table

App Development
1

I would like to determine an average in a table and use that number to calculate in another table. I don’t know how to do that.

Background:
User table with salaries.
I would like to determine an average salary for current employees only, who are in a particular department (so I have to add conditions).

In a second table I would like to calculate the cost for these type employees times the number of employees I forecast in the future.

Questions/Solution

  1. Do I need a rollup, a pipe or an equation? Or do I need make.com to do that? Do I need an automation to do this calculation daily?
  2. Is the average stored in the user table, the forecast table or should there be a table in between?

Thanks for thinking along!

2

@Peter
If you have a Users table and a Departments table, where each User is connected to a particular department, you can do the following:

In the Departments table, you can add:

  • a Rollup field to calculate the Average Employee Salary.
  • a Number field to store the Forecasted Number of Employees
  • An Equation field to find the Department Salary Forecast by multiplying the Average Salary by the Forecasted Number of Employees

image
image1920×410 52.2 KB

I hope this helps, let me know if you have additional questions.

3

@Sara thanks but that would mean rebuilding my app. I have department as a “select”, not as a separate table. What can you advice? Should I rebuild this to what you advice, or is there an alternative?

4

Hi @Peter -

Based on the limited information it sounds like you will have to rebuild your app and have departments as a parent to the employees/salaries. Then add a rollup filed as described previously.

5

I have my hopes for the sql-editor that seems to be developed in 2023. It should not be too difficult to construct a query to determine an avg in a table with some conditions added.

I have some work to do: rebuild my app.