title: sql iteration of same result set
body: <p>Why don't SQL provide functions to iterate query results more than once.  We may have windowing functions. But they are out of the box solutions. I need general Purpose iteration functions. </p>
  
  <p>Let me explain in detail,</p>
  
  <p>Assume example query and result as follows</p>
  
  <p> </p>
  
  <pre><code>select product p ,sum(sales) S 
  from Data group by product
  </code></pre>
  
  <p>Returns: </p>
  
  <pre><code> Product,sum(sales)
   coffee,10
   EnergyDrink,30
   Tea,20
  </code></pre>
  
  <p>Say i have requirement to get sum of other product total sales along each product. For example along cofee sales , i need sum tea and energy drink sales alone in other column</p>
  
  <pre><code>product         | sum sales | other sales
  coffee          |     10    |  50   &lt;30 +20&gt;
  EnergyDrink     |  30       |  30    &lt;10 (coffee)+20(tea)&gt;
  Tea             |  20       |  40   &lt;10+30&gt;
  </code></pre>
  
  <p>To achieve this in mysql ,</p>
  
  <p>Query i need is ,</p>
  
  <pre><code> select T1.p,T1.S,sum(T2.S) from
   (select product p ,sum(sales) S from Data group by product) T1
   JOIN
      (select product p ,sum(sales) S from Data group by product) T2
      on T1.p!=T2.p and T1.S != T2.S group by 1,2
  </code></pre>
  
  <p>Explain of the query is below ,</p>
  
  <p><img src="https://i.stack.imgur.com/d3Qum.png" alt="Explain as screenshot"></p>
  
  <p>Execution in mysql creates  3 derived temporary tables,</p>
  
  <p>But in programmatically i need to iterate the result of</p>
  
  <p><strong>select product p ,sum(sales) S from Data group by product</strong></p>
  
  <p>again in a inner loop. Say 3*3 iteration. Code way is as follows</p>
  
  <pre><code>for(int i=0;i&lt;3;i++)
  {
      int otherSales = 0;
      for(int j=0;j&lt;3;j++)
      {
        if(data[j].product != data[i].product) // if product is not same
        {
           otherSales += data[j].sales; // add that sales to other sales
        }
      }
  }
  </code></pre>
  
  <p><br>I suggest iterate behaviour in SQL, </p>
  
  <p><br>Assume SQL supports iterate function it enables iteration of result set again. And special function OUTER which gives access to outer loop access ('i'th loop value of product in 'j'th loop). If we have that we can have SQL like below,</p>
  
  <pre><code>select product p ,sum(sales) S ,
  <b>sum(iterate(if(outer(p)!=p,S,NULL)))</b>
  from Data group by product</code></pre>
  
  <p>here iterate function iterates result set again ,
  outer(p) gives access to product p in outerscope</p>
  
  <p>MS Power Pivot's DAX give such a function <a href="https://msdn.microsoft.com/en-us/library/ee634551.aspx" rel="nofollow noreferrer">EARLIER</a></p>
  
  <p>If we have similar function in SQL it will help for BI functions.</p>
  
  <p>SQL has LEAD , LAG  , RANK etc. But they are out of box SQL windowing functions  and it supports one level iteration. My suggestion is for general purpose iteration function it can be used for many purposes. </p>
  
  <p>My question is whether do we need <strong>ITERATION</strong> function? Or the database will handle such complex nested  (first example sql involving 2 level joins) with intelligence. How current scenario of execution of such queries in  Postgres , oracle etc ?Won't it create multiple copies of same result set as derived tables?</p>