title: SUM of metric for normalized logical hierarchy
body: <p>Suppose there's a following table Table1, describing parent-child relationship and metric:</p>
  
  <pre><code>Parent | Child | Metric (of a child)
  ------------------------------------
  name0 | name1 | a  
  name0 | name2 | b  
  name1 | name3 | c  
  name2 | name4 | d  
  name2 | name5 | e  
  name3 | name6 | f
  </code></pre>
  
  <p>Characteristics:<br>
  1) Child always has 1 and only 1 parent;<br>
  2) Parent can have multiple children (name2 has name4 and name5 as children);<br>
  3) Number of levels in this "hierarchy" and number of children for any given parent are arbitrary and do not depend on each other;</p>
  
  <p>I need SQL request that will return result set with each name and a sum of metric  <strong>of all its descendants down to the bottom level plus itself</strong>, so for this example table the result would be (look carefully at <strong>name1</strong>):</p>
  
  <pre><code>Name | Metric
  ------------------
  name1 | a + c + f  
  name2 | b + d + e  
  name3 | c + f  
  name4 | d  
  name5 | e  
  name6 | f
  </code></pre>
  
  <p>(name0 is irrelevant and can be excluded).</p>
  
  <p>It should be ANSI or Teradata SQL.</p>
  
  <p>I got as far as a recursive query that can return a SUM (metric) of all descendants of a given name:  </p>
  
  <pre><code>WITH RECURSIVE temp_table (Child, metric) AS
  (  
     SELECT root.Child, root.metric  
     FROM table1 root  
     WHERE root.Child = 'name1'  
     UNION ALL  
     SELECT indirect.Child, indirect.metric  
     FROM temp_table direct, table1 indirect  
     WHERE direct.Child = indirect.Parent
  )  
  SELECT SUM(metric) FROM temp_table;  
  </code></pre>
  
  <p>Is there a way to turn this query into a function that takes name as an argument and returns this sum, so it can be called like this?</p>
  
  <pre><code>SELECT Sum_Of_Descendants (Child) FROM Table1;
  </code></pre>
  
  <p>Any suggestions about how to approach this from a different angle would be appreciated as well, because even if the above way is implementable, it will be of poor performance - there would be a lot of iterations of reading metrics (value f would be read 3 times in this example). Ideally, the query should read a metric of each name only once.</p>