title: Cumulative number of files in subfolders
body: <p>I do have a table with list of files. There is id_folder, id_parrent_folder, size (file size):</p>
  
  <pre><code>create table sample_data (
      id_folder bigint ,
      id_parrent_folder bigint,
      size bigint
  );
  </code></pre>
  
  <p>I would like to know, how many files are in every subfolder (including current folder) for each folder (starting wigh given folder). Given the samle data posted below I expect the following output:</p>
  
  <pre><code>id_folder     files
  100623           35
  100624           14
  </code></pre>
  
  <p>Sample data:</p>
  
  <pre><code>insert into sample_data values (100623,58091,60928);
  insert into sample_data values (100623,58091,59904);
  insert into sample_data values (100623,58091,54784);
  insert into sample_data values (100623,58091,65024);
  insert into sample_data values (100623,58091,25600);
  insert into sample_data values (100623,58091,31744);
  insert into sample_data values (100623,58091,27648);
  insert into sample_data values (100623,58091,39424);
  insert into sample_data values (100623,58091,30720);
  insert into sample_data values (100623,58091,71168);
  insert into sample_data values (100623,58091,68608);
  insert into sample_data values (100623,58091,34304);
  insert into sample_data values (100623,58091,46592);
  insert into sample_data values (100623,58091,35328);
  insert into sample_data values (100623,58091,29184);
  insert into sample_data values (100623,58091,38912);
  insert into sample_data values (100623,58091,38400);
  insert into sample_data values (100623,58091,49152);
  insert into sample_data values (100623,58091,14444);
  insert into sample_data values (100623,58091,33792);
  insert into sample_data values (100623,58091,14789);
  insert into sample_data values (100624,100623,16873);
  insert into sample_data values (100624,100623,32768);
  insert into sample_data values (100624,100623,104920);
  insert into sample_data values (100624,100623,105648);
  insert into sample_data values (100624,100623,31744);
  insert into sample_data values (100624,100623,16431);
  insert into sample_data values (100624,100623,46592);
  insert into sample_data values (100624,100623,28160);
  insert into sample_data values (100624,100623,58650);
  insert into sample_data values (100624,100623,162);
  insert into sample_data values (100624,100623,162);
  insert into sample_data values (100624,100623,162);
  insert into sample_data values (100624,100623,162);
  insert into sample_data values (100624,100623,162);
  </code></pre>
  
  <p>I've tried to use example from postgresql (<a href="http://www.postgresql.org/docs/9.1/static/queries-with.html" rel="nofollow">postgresql docs</a>), but it (obviously) can't work this way. Any help appreciated.</p>
  
  <p>-- Edit</p>
  
  <p>I've tried the following query:</p>
  
  <pre><code>WITH RECURSIVE included_files(id_folder, parrent_folder, dist_last_change) AS (
  SELECT 
      id_folder, 
      id_parrent_folder, 
      size
  FROM 
      sample_data p 
  WHERE 
      id_folder = 100623
  UNION ALL
  SELECT 
      p.id_folder, 
      p.id_parrent_folder, 
      p.size
  FROM 
      included_files if, 
      sample_data p
  WHERE 
      p.id_parrent_folder = if.id_folder
  )
  select * from included_files
  </code></pre>
  
  <p>This won't work, because for every child there is a lot of parents and as a result rows in child folders are multiplied.</p>