title: Using partial results from a query to calculate other rows of the same query
body: <p>I have a hierarchical structure that comes from a list of project tasks and subtasks.  Subtasks can have also subtasks.  </p>
  
  <p>A task <code>prwbssequence</code> is its sequence number and a task <code>wbs_parseq</code> is the same as its parent <code>prwbssequence</code>. </p>
  
  <p>All this information is on a Oracle table (<code>prtask</code>). Tasks that do not have subtasks (leafs) have <code>pristask</code> value = 0, and those that have (parent) have <code>pristask = 1</code>. I need to calculate the percent effort of all parent tasks.  This is the sum of the percent effort for its substask. </p>
  
  <p>Bellow is the query I´d like to work (but doesn´t).  The subquery has sum(total_effort) and this is actually the result of the subquery.  What I would like this to do is calculate the total_effort for a parent task and then use this value to calculate it´s parent effort. Any idea on how to do this?</p>
  
  <pre><code>SELECT task.prprojectid, task.prname, task.pristask, 
  DECODE (task.pristask, 
            1, task.percent_effort,
          0, (SELECT sum(total_effort)
                FROM prtask t2,
                WHERE prprojectid = task.prprojectid and task.pristask = 0 AND wbs_parseq =                   prwbssequence
                GROUP BY wbs_parseq)
          ) total_effort
  FROM prtask task
  AND task.prprojectid = 5008045
  ORDER BY task.prwbssequence desc
  </code></pre>