Q:

Query to show sum of all earnings at the all lower levels of hierarchy of people for each person

I have an Oracle db that contains tables People, and Earnings. Each person have some leader, and many people could have the same leader. Also a leader can have his own leader. So there is a hierarchy. Here is the example with the query that i have now 
http://sqlfiddle.com/#!4/15e85/3
I need to create a query that will count the sum of earnings all subordinates (on all lower levels of hierarchy) for each person. So the sum for the main leader person will be the sum of all earnings except his earnings. How to achieve this? 

A:

SELECT p.root, sum(e.EARNINGVALUE) s 
FROM Earnings e JOIN
(SELECT p.IdPerson,
        connect_by_root p.IdPerson root,
        connect_by_isleaf lf
 FROM People p
 CONNECT BY PRIOR p.IdPerson= p.IdLeader
) p on p.IdPerson = e.IdPerson
where root != p.IdPerson
        -- uncomment this if you want to calculate earnings of people who don't have subordinates 
        -- or lf = 1
GROUP BY root;

The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a
  leaf of the tree defined by the CONNECT BY condition. Otherwise it
  returns 0. This information indicates whether a given row can be
  further expanded to show more of the hierarchy.
CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator,
  Oracle returns the column value using data from the root row. This
  operator extends the functionality of the CONNECT BY [PRIOR] condition
  of hierarchical queries.
Oracle processes hierarchical queries as follows:   

A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.   
The CONNECT BY condition is evaluated.   
Any remaining WHERE clause predicates are evaluated.

If you don't specify START WITH Oracle starts with each row. So for each row you can find earnings of its subordinates.
Here is the version without CONNECT_BY_ROOT:
with tmp_tree as (
  SELECT p.IdPerson, rownum rw, level lvl
  FROM People p
  CONNECT BY PRIOR p.IdPerson = p.IdLeader
),
tmp_tree_with_root as (
  SELECT t1.IdPerson, t2.IdPerson root
  FROM (SELECT IdPerson, min(rw) over(partition by root_group) root_rw
          FROM (SELECT IdPerson, rw, 
                       rw - sum(case when lvl > 1 then 1 else 0 end) 
                            over(order by rw) root_group
                FROM tmp_tree) 
        ) t1
        join tmp_tree t2
        on t1.root_rw = t2.rw
) 
SELECT p.root, sum(e.EARNINGVALUE) s 
FROM Earnings e JOIN tmp_tree_with_root p
     on p.IdPerson = e.IdPerson
where root != p.IdPerson
GROUP BY root;

1) Use rownum to mark each row in hierarchy
2) Use analytic rw - sum(case when lvl > 1 then 1 else 0 end) over(order by rw) to define groups (each group is a hierarchy)
3) tmp_tree_with_root gives the same result as the subquery with CONNECT_BY_ROOT

