How would one find the total cases by manager

I have two tables: one that has cases alongside the employees who resolve them and one that has information on the employees including their managers. I want to make a query that finds the amount of cases under each manager. Currently my query looks like this

select m.id, COUNT(distinct case when e.manager_id = m.id and a.resolver_id = e.id then a.ticket_id ELSE NULL END)
from tickets a 
left join employee_info e on a.resolver_id= e.id
join employee_info m on e.manager_id = m.id
group by m.id

This query only gave me the count of the direct employees under a manager, not the count of all employees (including the ones under the people reporting directly to the manager). How would I tweak my query to include all employees under a person?

EDIT

So this is an obfuscated example of how my tables look

Ticket_id resolver_id
0001      11
0002      11
0003      13
0004      13
0005      12
0006      19

Id manager_id
11 01
12 01
13 11
19 12

and this is how I want my result to look

Id count
01 6
11 4
12 2
13 2
19 1

Solution

You essentially have a "tree" of employees, and want to recursively traverse it. This is what recursive CTEs are for.

We'll define a "subordinate" cte, that will tell us all the "subordinate relationships" between all employees, including "multi-level" subordination. If A is manager of B, and B is manager of C, then A has as subordinates B and C.

First, we start with all employees being their own subordinates. This is the non-recursive part.

    SELECT id AS id1, id AS id2
    FROM employee

Then, we "expand one level down" the subordinate relationships. if B is subordinate of A, all employees with B as manager are also subordinates of A. id1 stys as-is, id2 becomes the id of the "lower" employee. This is the recursive part.

    SELECT s.id1, e.id
    FROM subordinate s
    JOIN employee e ON s.id2 = e.manager_id

Then we stick both in a recursive CTE. Postgres will iterate the 2nd part as many times as needed, until no new rows are added. This way we recusrively traverse the entire employee tree.

WITH RECURSIVE subordinate AS (
    SELECT id AS id1, id AS id2
    FROM employee
UNION
    SELECT s.id1, e.id
    FROM subordinate s
    JOIN employee e ON s.id2 = e.manager_id
)
select * from subordinate order by id1, id2;

Let's check the result:

 id1 | id2 
-----+-----
   1 |   1
   1 |  11
   1 |  12
   1 |  13
   1 |  19
  11 |  11
  11 |  13
  12 |  12
  12 |  19
  13 |  13
  19 |  19

Looking great! 1 has everyone as subordinates. 11 has 11 and 13, and lowst employees such as 13 and 19 only have themselves.

Once we have done this, the rest is easy.

We can do another CTE counting the resolved tickets per employee:

    SELECT resolver_id as id, COUNT(*) as count
    FROM tickets
    GROUP BY resolver_id

and then we stick everything into the final query. For every employee, sum the resolved count of all its subordinates.

WITH RECURSIVE subordinate AS (
    SELECT id AS id1, id AS id2
    FROM employee
UNION
    SELECT s.id1, e.id
    FROM subordinate s
    JOIN employee e ON s.id2 = e.manager_id
),
resolved as (
    SELECT resolver_id as id, COUNT(*) as count
    FROM tickets
    GROUP BY resolver_id
)
SELECT s.id1, SUM(r.count)
FROM subordinate s
JOIN resolved r ON r.id = s.id2
GROUP BY s.id1;