Searching for a record in the entire table

[Awani]

I have a table that has the columns employee_name, employee_id, age, gender, manager_name, manager_id.

The manager is of course also an employee and their manager_id is the same as their employee_id. That is, if I have a manager called Ruth, their manager_id let's say 2327 is the same as their employee_id.

I want to get a count of all the managers(which I can do by counting the manager_ids) but I also want to know how old they are and what are their genders. 

How can I kindly do this? 

[vincent_ardiet_]

If for example you have your existing table called "Employees" you can create another table "Managers" derived from the first one:

Managers:
Load Distinct 
    employee_name as manager_name
  ,employee_id as manager_id
  ,age as manager_age
  ,gender as manager_gender
resident Employees
where Exists(employee_id, manager_id);

Drop Field manager_name From Employees;

[Awani]

Hi, I've done this but it's bringing me a table with the age and gender of the employees that are under the manager. But what I want is the age and gender of the manager.

[vincent_ardiet_ · Accepted solution]

Sorry my bad, I have inverted the fields in the Exists clause (usual mistake):

Managers:
Load Distinct 
    employee_name as manager_name
  ,employee_id as manager_id
  ,age as manager_age
  ,gender as manager_gender
resident Employees
where Exists(manager_id, employee_id);

Drop Field manager From Employees;

[Awani]

Hi It has worked. Thanks alot. I appreciate it.