Combine the result of one sql query into another

Question

I have the below table.

CREATE TABLE Employee_id_credits ( Employee_id, credits ) AS
  SELECT 10, 1 FROM DUAL UNION ALL
  SELECT 12, 1 FROM DUAL UNION ALL
  SELECT 10, 1 FROM DUAL UNION ALL
  SELECT 12, 1 FROM DUAL UNION ALL
  SELECT 12, 1 FROM DUAL UNION ALL
  SELECT 14, 1 FROM DUAL;

The below query groups and counts the total number of credits for employees.

select Employee_id, count(*) as "Total_credits"
from Employee_id_credits
group by Employee_id;

Gives the below output.

Employee_id Total_credits
----------- -------------
         10             2
         12             3
         14             1

I have a Employee Manager table with the hierarchy.

CREATE TABLE Employee_Manager ( Employee_id, Manager_id ) AS
  SELECT  10, 101  FROM DUAL UNION ALL
  SELECT  12, 120  FROM DUAL UNION ALL
  SELECT  13, 120  FROM DUAL UNION ALL
  SELECT  14, 150  FROM DUAL UNION ALL
  SELECT 101, NULL FROM DUAL UNION ALL
  SELECT 120, 130  FROM DUAL UNION ALL
  SELECT 130, NULL FROM DUAL;

I have a query to find the top level manager of the employee.

SELECT
  Employee_id
FROM 
  Employee_Manager
WHERE
  Manager_id is null
CONNECT BY PRIOR
  Manager_id = Employee_id
START WITH
  Employee_id = '12';

I want to combine the above two queries so that the output would look like below. How do I combine both queries?

Manager Total_credits
------- -------------
    101             2
    130             3
    150             1

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Employee_id_credits ( Employee_id, credits ) AS
  SELECT 10, 1 FROM DUAL UNION ALL
  SELECT 12, 1 FROM DUAL UNION ALL
  SELECT 10, 1 FROM DUAL UNION ALL
  SELECT 12, 1 FROM DUAL UNION ALL
  SELECT 12, 1 FROM DUAL UNION ALL
  SELECT 14, 1 FROM DUAL;

CREATE TABLE Employee_Manager ( Employee_id, Manager_id ) AS
  SELECT  10, 101  FROM DUAL UNION ALL
  SELECT  12, 120  FROM DUAL UNION ALL
  SELECT  13, 120  FROM DUAL UNION ALL
  SELECT  14, 150  FROM DUAL UNION ALL
  SELECT 101, NULL FROM DUAL UNION ALL
  SELECT 120, 130  FROM DUAL UNION ALL
  SELECT 130, NULL FROM DUAL;

Query 1 - Find the managers of each employee:

SELECT CONNECT_BY_ROOT( Employee_id ) AS Employee_id,
       COALESCE( manager_id, employee_id ) AS manager_id
FROM   Employee_manager
WHERE  CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR Manager_id = Employee_id

Results:

| EMPLOYEE_ID | MANAGER_ID |
|-------------|------------|
|          10 |        101 |
|          12 |        130 |
|          13 |        130 |
|          14 |        150 |
|         101 |        101 |
|         120 |        130 |
|         130 |        130 |

Query 2 - Join that with the credits table and aggregate:

SELECT m.manager_id,
       SUM( c.credits ) As total_credits
FROM   Employee_id_credits c
       INNER JOIN
       (
         SELECT CONNECT_BY_ROOT( Employee_id ) AS Employee_id,
                COALESCE( manager_id, employee_id ) AS manager_id
         FROM   Employee_manager
         WHERE  CONNECT_BY_ISLEAF = 1
         CONNECT BY PRIOR Manager_id = Employee_id
       ) m
       ON ( c.employee_id = m.employee_id )
GROUP BY m.manager_id

Results:

| MANAGER_ID | TOTAL_CREDITS |
|------------|---------------|
|        101 |             2 |
|        130 |             3 |
|        150 |             1 |