title: subquery with aggregate/analytic function in Hiveql
body: <p>I'm wondering if this is legal: </p>
  
  <pre><code>INSERT OVERWRITE TABLE my_table
    SELECT field_1, field_2, 
       (SELECT STDDEV(field_from_my_table) 
        FROM my_table 
        WHERE field_from_my_table !=0)
    FROM my_table
    GROUP BY field_1;
  </code></pre>
  
  <p>So far, I haven't been able to get it to work. I get a myriad of errors depending on what I try, mostly random errors indicating illegal syntax. Eg.</p>
  
  <pre><code>cannot recognize input near 'SELECT' 'STDDEV' 
  </code></pre>
  
  <p>This, however, does work:</p>
  
  <pre><code> INSERT OVERWRITE TABLE my_table
    SELECT field_1, field_2, STDDEV(field_from_my_table) 
    FROM my_table
    GROUP BY field_1;
  </code></pre>
  
  <p>So basically, I'm just changing how I'd like to use STDDEV - nothing else is different between the version that works and the one that doesn't.</p>
  
  <p>If it's not possible to do it this way, why not? If it is, what corrections do I need? Any help would be greatly appreciated. Thanks. </p>
  
  <p>EDIT: Possibly this <a href="https://stackoverflow.com/questions/13631259/nested-queries-in-hive-sql#13643328">Nested queries in Hive SQL</a>, this <a href="https://stackoverflow.com/questions/10710075/hive-column-as-a-subquery-select">Hive column as a subquery select</a>, or this <a href="https://stackoverflow.com/questions/22059559/does-hive-support-select-in-select/22059792#22059792">Does Hive support Select in Select?</a></p>
  
  <p>EDIT2: So I think the easier question might be:
  this works,</p>
  
  <pre><code> INSERT OVERWRITE TABLE my_table
    SELECT field_1, field_2, STDDEV(field_from_my_table) 
    FROM my_table
    GROUP BY field_1;
  </code></pre>
  
  <p>And this works (as an interactive query)</p>
  
  <pre><code>SELECT field_1, STDDEV(field_from_my_table) 
  FROM my_table
  WHERE field_from_my_table !=0
  GROUP BY field_1;
  </code></pre>
  
  <p>How do I combine the two into a single table (new or old)? </p>