Correlated query without SQL widgets

Correlated query without SQL widgets

  

Would someone show me how to implement Aggregate on an Aggregate or equivalent based on the below correlated query example without the use of SQL widgets. Im trying to avoid using the platform (ie MSSQL or Oracle) based SQL language.

SELECT employee_number, name
   FROM employees emp
   WHERE salary > (
     SELECT AVG(salary)
       FROM employees
       WHERE department = emp.department);

Hi, you can create two aggregates:

1. First one to select the average

2. Second one to filter using the result above.

Solution

Hello Anthony and Pasar,

I think there is a way to do it with a single Aggregate.

First, let's rewrite your query using JOIN, as aggregates can't have a subquery.

SELECT {Employee}.[Employee_Number], {Employee}.[Salary]
FROM {Employee}
    INNER JOIN {Employee} dept ON dept.[Department] = {Employee}.[Department]
GROUP BY {Employee}.[Employee_Number], {Employee}.[Salary]
HAVING {Employee}.[Salary] > avg(dept.[Salary])

As you can see above, every employee will be joined all employees of the same department.
You group by employee number and salary and do a group filter to show only the employees that have a salary bigger than the average of the department employees.

Now you can do the exact same thing in an aggregate.

1. In an aggregate, add the employee twice (using Add Source):

Notice that no JOIN is made.

2. Add a Join by the Department (I changed the name of Employee_2 to DeptEmployee).

Here I used an INNER JOIN (Only With), so the order does not matter. But maybe you will need to change the order if maybe an employee is not in a Department, and you want to use a LEFT join (as the important part that must be the Employee).

3. Add the Group By for the information you wanna retrieve (in this case, employee number and salary), and also the average of the DeptEmployee salary (that we will need to the group filter).

Now you get back on line per employee, with all the related employees (same department) associated to each one, and the average of the associate employees' salaries. 

4. Add the group filter.

Conclusion.

So, I didn't test it, but I think it will work.
Hope this helps.

Cheers.

Solution

Awesome! It worked! Thanks very much for you help.