SQL query to get the subtotal of some rows

Question

What would be the SQL query script if I want to get the total items and total revenue for each manager including his team?
Suppose I have this table items_revenue with columns:

| id |is_manager|manager_id| name |no_of_items| revenue | | 1 | 1 | 0 | Manager1 | 621 | 833 | | 2 | 1 | 0 | Manager2 | 458 | 627 | | 3 | 1 | 0 | Manager3 | 872 | 1027 | ... | 8 | 0 | 1 | Member1 | 1258 | 1582 | | 9 | 0 | 2 | Member2 | 5340 | 8827 | | 10 | 0 | 3 | Member3 | 3259 | 5124 |

All the managers and their respective members are in the above view table. Member1 is under Manager1, Member2 is under Manager2, and so on, but real data are in random arrangement. I also have a view table with managers where I filtered only the managers. I tried getting the totals per team manager with this query:
SELECT id, is_manager, manager_id, name, SUM(no_of_items) AS total_items, SUM(revenue) AS total_revenue FROM items_revenue t WHERE (SELECT m.id FROM managers m WHERE m.id = t.id) GROUP BY id HAVING id IN (SELECT m.id FROM managers m);

However, it only returns the totals of each manager alone, it does not get the totals of their members.

I want my query to output the ff. table:
| id |is_manager|manager_id| name |total_items| total_revenue | | 1 | 1 | 0 | Manager1 | 1879 | 2415 | | 2 | 1 | 0 | Manager2 | 5798 | 9454 | | 3 | 1 | 0 | Manager3 | 4131 | 6151 |

Any ideas how to get this? Any help would be greatly appreciated. Thanks!

Answer 1

You can aggregate using a CASE expression, which forms a group using the id if the manager_id be zero, otherwise using the manager_id. The rest of the logic is similar to what you already have.

SELECT
    CASE WHEN manager_id = 0 THEN id ELSE manager_id END AS manager_id,
    MAX(CASE WHEN is_manager=1 THEN name END) AS name,
    SUM(no_of_items) AS total_items,
    SUM(revenue) AS total_revenue
FROM items_revenue
GROUP BY
    CASE WHEN manager_id = 0 THEN id ELSE manager_id END;

Demo

One side note: I used a function in the GROUP BY clause, which is not ANSI compliant and therefore may not run everywhere. To fix this, we can first subquery your table to generate the effective manager groups. Then, use my above answer against this intermediate result.