title: Netezza SQL For Loop
body: <p>I have the following query Which I will call query1:</p>
  
  <pre><code>with a as (
  select customer_key as cust,
  sum(sales)*1.0/4 as avg_sales
  sum(returns)*1.0/4 as avg_return
  count(distinct order_key)*1.04 as avg_num_orders
  from orders_table
  where purch_year between 2011 and 2014
  group by cust
  order by random()
  ),
  
  b as (
  select *
  from a
  where avg_num_orders &gt; .25
  limit 100000
  )
  
  select case 
         when avg_num_orders &lt;= 1 then 'Low'
         when avg_num_orders between 1 and 4 then 'Medium'
         when avg_num_orders &gt; 4 then 'High'
     end as estimated_frequency,
     count(cust) as num_purchasers_year,
     sum(avg_num_orders) as num_orders_year,
     avg(avg_num_orders) as avg_num_order_year,
     sum(avg_sales) as avg_sales_year,
     sum(avg_total_return) as avg_return_year,
     avg_sales_year/num_orders_year as AOV,
     avg_sales_year/num_purchasers_year as ACS,
     stddev(avg_sales) as sales_stddev
  from b
  where avg_num_orders &gt; .25
  group by estimated_frequency
  order by  estimated_frequency;
  </code></pre>
  
  <p>I want to write code that does the following (this is what does not work, I have provided pseudocode). I do not have permission to create a procedure.</p>
  
  <pre><code>Create table temp1
  for i in 1..100 loop
  insert into temp1 the result of QUERY1
  end loop
  </code></pre>
  
  <p>then</p>
  
  <pre><code>select estimated_frequency,
         avg(acs),
         avg(sales_stddev)
  from temp1
  group by estimated_frequency 
  </code></pre>
  
  <p>Essentially, I want to run query1 100 times, and store the results in a table called temp1, and then compute some averages on temp1 once i am all said and done.</p>
  
  <p>Thank you for your help</p>