<p>I have a table similar to this:</p>

<pre><code>CREATE TABLE example (
  id integer primary key,
  name char(200),
  parentid integer,
  value integer);
</code></pre>

<p>I can use the parentid field to arrange data into a tree structure.</p>

<p>Now here's the bit I can't work out. Given a parentid, is it possible to write an SQL statement to add up all the value fields under that parentid and recurse down the branch of the tree ?</p>

<p><strong>UPDATE:</strong> I'm using posgreSQL so the fancy MS-SQL features are not available to me. In any case, I'd like this to be treated as a generic SQL question.</p>

<p>BTW, I'm very impressed to have 6 answers within 15 minutes of asking the question! Go stack overflow!</p>

[{'answer_id': 53119, 'author': 'George Mauer', 'author_id': 5056, 'author_profile': 'https://Stackoverflow.com/users/5056', 'pm_score': 0, 'selected': False, 'text': "<p>is this SQL Server?  Couldn't you write a  TSQL stored procedure that loops through and unions the results together?  </p>\n\n<p>I am also interested if there is a SQL-only way of doing this though.  From the bits  I remember from my geographic databases class, there should be.</p>\n"}, {'answer_id': 53121, 'author': 'Darren Kopp', 'author_id': 77, 'author_profile': 'https://Stackoverflow.com/users/77', 'pm_score': 3, 'selected': False, 'text': '<p>use a <a href="http://msdn.microsoft.com/en-us/library/ms186243.aspx" rel="nofollow noreferrer">common table expression</a>. </p>\n\n<blockquote>\n  <p>May want to indicate this is SQL Server 2005 or above only. <a href="https://stackoverflow.com/users/1117/dale-ragan">Dale Ragan</a></p>\n</blockquote>\n\n<p><a href="http://www.sqlteam.com/article/more-trees-hierarchies-in-sql" rel="nofollow noreferrer">here\'s an article</a> on recursion by SqlTeam without common table expressions.</p>\n'}, {'answer_id': 53129, 'author': 'Gulzar Nazim', 'author_id': 4337, 'author_profile': 'https://Stackoverflow.com/users/4337', 'pm_score': 0, 'selected': False, 'text': '<p>I think it is easier in SQL 2008 with <a href="http://msdn.microsoft.com/en-us/magazine/cc794278.aspx" rel="nofollow noreferrer">HierarchyID</a></p>\n'}, {'answer_id': 53130, 'author': 'FlySwat', 'author_id': 1965, 'author_profile': 'https://Stackoverflow.com/users/1965', 'pm_score': 3, 'selected': False, 'text': '<p>If your using SQL Server 2005, there is a really cool way to do this using Common Table Expressions.</p>\n\n<p>It takes all of the gruntwork out of creating a temporary table, and basicly allows you to do it all with just a WITH and a UNION.</p>\n\n<p>Here is a good tutorial:</p>\n\n<p><a href="http://searchwindevelopment.techtarget.com/tip/0,289483,sid8_gci1278207,00.html" rel="nofollow noreferrer">http://searchwindevelopment.techtarget.com/tip/0,289483,sid8_gci1278207,00.html</a></p>\n'}, {'answer_id': 53137, 'author': 'jason saldo', 'author_id': 1293, 'author_profile': 'https://Stackoverflow.com/users/1293', 'pm_score': 1, 'selected': False, 'text': '<p>Oracle has "START WITH" and "CONNECT BY"  </p>\n\n<pre><code>select \n    lpad(\' \',2*(level-1)) || to_char(child) s\n\nfrom \n    test_connect_by \n\nstart with parent is null\nconnect by prior child = parent;\n</code></pre>\n\n<p><a href="http://www.adp-gmbh.ch/ora/sql/connect_by.html" rel="nofollow noreferrer">http://www.adp-gmbh.ch/ora/sql/connect_by.html</a></p>\n'}, {'answer_id': 53158, 'author': 'Portman', 'author_id': 1690, 'author_profile': 'https://Stackoverflow.com/users/1690', 'pm_score': 4, 'selected': False, 'text': '<p>If you want a portable solution that will work on any ANSI <a href="http://en.wikipedia.org/wiki/SQL-92" rel="noreferrer">SQL-92</a> RDBMS, you will need to add a new column to your table.</p>\n\n<p>Joe Celko is the original author of the <strong><a href="http://www.intelligententerprise.com/001020/celko.jhtml" rel="noreferrer">Nested Sets</a></strong> approach to storing hierarchies in SQL. You can Google <a href="http://www.google.com/search?q=%22nested+sets%22+hierarchy" rel="noreferrer">"nested sets" hierarchy</a> to understand more about the background.</p>\n\n<p>Or you can just rename parentid to <strong>leftid</strong> and add a <strong>rightid</strong>. </p>\n\n<p>Here is my attempt to summarize Nested Sets, which will fall woefully short because I\'m no Joe Celko: SQL is a set-based language, and the adjacency model (storing parent ID) is NOT a set-based representation of a hierarchy. Therefore there is no pure set-based method to query an adjacency schema.</p>\n\n<p><strong>However</strong>, most of the major platforms have introduced extensions in recent years to deal with this precise problem. So if someone replies with a Postgres-specific solution, use that by all means.</p>\n'}, {'answer_id': 53223, 'author': 'Jacob Rigby', 'author_id': 5357, 'author_profile': 'https://Stackoverflow.com/users/5357', 'pm_score': -1, 'selected': False, 'text': '<p>If you need to store arbitrary graphs, not just hierarchies, you could push Postgres to the side and try a graph database such as <a href="http://agraph.franz.com/support/learning/" rel="nofollow noreferrer">AllegroGraph</a>:</p>\n\n<p>Everything in the graph database is stored as a triple (source node, edge, target node) and it gives you first class support for manipulating the graph structure and querying it using a SQL like language. </p>\n\n<p>It doesn\'t integrate well with something like Hibernate or Django ORM but if you are serious about graph structures (not just hierarchies like the Nested Set model gives you) check it out. </p>\n\n<p>I also believe Oracle has finally added a support for real Graphs in their latest products, but I\'m amazed it\'s taken so long, lots of problems could benefit from this model.</p>\n'}, {'answer_id': 54362, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 5, 'selected': True, 'text': '<p>There are a few ways to do what you need in PostgreSQL.</p>\n\n<ul>\n<li><p>If you can install modules, look at the tablefunc contrib. It has a connectby() function that handles traversing trees. <a href="http://www.postgresql.org/docs/8.3/interactive/tablefunc.html" rel="noreferrer">http://www.postgresql.org/docs/8.3/interactive/tablefunc.html</a></p></li>\n<li><p>Also check out the ltree contrib, which you could adapt your table to use: <a href="http://www.postgresql.org/docs/8.3/interactive/ltree.html" rel="noreferrer">http://www.postgresql.org/docs/8.3/interactive/ltree.html</a></p></li>\n<li><p>Or you can traverse the tree yourself with a PL/PGSQL function.</p></li>\n</ul>\n\n<p>Something like this:</p>\n\n<pre><code>create or replace function example_subtree (integer)\nreturns setof example as\n\'declare results record;\n         child record;\n begin\n  select into results * from example where parent_id = $1;\n  if found then\n    return next results;\n    for child in select id from example\n                  where parent_id = $1\n      loop\n        for temp in select * from example_subtree(child.id)\n        loop\n          return next temp;\n        end loop;\n      end loop;\n  end if;\n  return null;\nend;\' language \'plpgsql\';\n\nselect sum(value) as value_sum\n  from example_subtree(1234);\n</code></pre>\n'}, {'answer_id': 508921, 'author': 'Richard Gomes', 'author_id': 62131, 'author_profile': 'https://Stackoverflow.com/users/62131', 'pm_score': 2, 'selected': False, 'text': '<p>The following code compiles and it\'s tested OK.</p>\n\n<pre>\ncreate or replace function subtree (bigint)\nreturns setof example as $$\ndeclare\n    results record;\n    entry   record;\n    recs    record;\nbegin\n    select into results * from example where parent = $1;\n    if found then\n        for entry in select child from example where parent = $1 and child  parent loop\n            for recs in select * from subtree(entry.child) loop\n                return next recs;\n            end loop;\n        end loop;\n    end if;\n    return next results;\nend;\n$$ language \'plpgsql\';\n</pre>\n\n<p>The condition "child &lt;> parent" is needed in my case because nodes point to themselves.</p>\n\n<p>Have fun :)</p>\n'}, {'answer_id': 548619, 'author': 'Chris KL', 'author_id': 58110, 'author_profile': 'https://Stackoverflow.com/users/58110', 'pm_score': 5, 'selected': False, 'text': '<p>Since version 8.4, PostgreSQL has <a href="http://www.postgresql.org/docs/current/static/queries-with.html" rel="noreferrer">recursive query support</a> for common table expressions using the SQL standard <code>WITH</code> syntax.</p>\n'}, {'answer_id': 656309, 'author': 'Dr.Pil', 'author_id': 74881, 'author_profile': 'https://Stackoverflow.com/users/74881', 'pm_score': 1, 'selected': False, 'text': "<p>Just as a brief aside although the question has been answered very well, it should be noted that if we treat this as a:</p>\n\n<blockquote>\n  <p>generic SQL question</p>\n</blockquote>\n\n<p>then the SQL implementation is fairly straight-forward, as SQL'99 allows linear recursion in the specification (although I believe no RDBMSs implement the standard fully) through the <code>WITH RECURSIVE</code> statement. So from a theoretical perspective we can do this right now.</p>\n"}, {'answer_id': 4659648, 'author': 'Slawa', 'author_id': 417153, 'author_profile': 'https://Stackoverflow.com/users/417153', 'pm_score': 1, 'selected': False, 'text': "<p>None of the examples worked OK for me so I've fixed it like this:</p>\n\n<pre>\ndeclare\n    results record;\n    entry   record;\n    recs    record;\nbegin\n    for results in select * from project where pid = $1 loop\n        return next results;\n        for recs in select * from project_subtree(results.id) loop\n            return next recs;\n        end loop;\n    end loop;\n    return;\nend;\n</pre>\n"}, {'answer_id': 4659750, 'author': 'Quassnoi', 'author_id': 55159, 'author_profile': 'https://Stackoverflow.com/users/55159', 'pm_score': 3, 'selected': False, 'text': '<p>A standard way to make a recursive query in <code>SQL</code> are recursive <code>CTE</code>. <code>PostgreSQL</code> supports them since <code>8.4</code>.</p>\n\n<p>In earlier versions, you can write a recursive set-returning function:</p>\n\n<pre><code>CREATE FUNCTION fn_hierarchy (parent INT)\nRETURNS SETOF example\nAS\n$$\n        SELECT  example\n        FROM    example\n        WHERE   id = $1\n        UNION ALL\n        SELECT  fn_hierarchy(id)\n        FROM    example\n        WHERE   parentid = $1\n$$\nLANGUAGE \'sql\';\n\nSELECT  *\nFROM    fn_hierarchy(1)\n</code></pre>\n\n<p>See this article:</p>\n\n<ul>\n<li><a href="http://explainextended.com/2009/05/29/hierarchical-queries-in-postgresql/" rel="noreferrer"><strong>Hierarchical queries in PostgreSQL</strong></a></li>\n</ul>\n'}, {'answer_id': 5701124, 'author': 'Endy Tjahjono', 'author_id': 196451, 'author_profile': 'https://Stackoverflow.com/users/196451', 'pm_score': 5, 'selected': False, 'text': "<p>Here is an example script using common table expression:</p>\n\n<pre><code>with recursive sumthis(id, val) as (\n    select id, value\n    from example\n    where id = :selectedid\n    union all\n    select C.id, C.value\n    from sumthis P\n    inner join example C on P.id = C.parentid\n)\nselect sum(val) from sumthis\n</code></pre>\n\n<p>The script above creates a 'virtual' table called <code>sumthis</code> that has columns <code>id</code> and <code>val</code>. It is defined as the result of two selects merged with <code>union all</code>.</p>\n\n<p>First <code>select</code> gets the root (<code>where id = :selectedid</code>).</p>\n\n<p>Second <code>select</code> follows the children of the previous results iteratively until there is nothing to return.</p>\n\n<p>The end result can then be processed like a normal table. In this case the val column is summed.</p>\n"}]
['https://Stackoverflow.com/questions/53108', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5324/']