title: Breaking the results of an sql like query into groups by percentiles : In Redshift / postgresql
body: <p>I have a collection of group_name with their counts. Say this comes from the statement as below -:</p>
  
  <pre><code>--sample input set --
  select group_name, count(*) as group_count 
       from mytable group by group_name 
       order by group_count desc ;
  
      group_name  group_count 
      A 205
      B 200
      C 67
      D 55
      E 50 
      F 12
      and so on..
  </code></pre>
  
  <p>What I want is to organize the results of groups_counts with their group names into 3 groups, say Head, core &amp; tail - <strong>where each group is understood to be in 33% of the total group_count</strong>.So 10, 5 and so on will be replaced by their respective percentiles. And all of this I need to do in redshift(postgres 8.0.2 )</p>
  
  <p>As a first level, it should be something like, </p>
  
  <p><strong>-- this is not a valid syntax--</strong></p>
  
  <pre><code>select case when group_count  &gt;10 then group_name end ) as Head_group,
  case when group_count  &gt;5  and group_count &lt;10 then query end ) as core_group, 
  case when group_count  &lt;5   then group_name end ) as tail_group, 
   from 
   ( select group_name, count(*) as group_count 
   from mytable group by group_name 
   order by group_count desc ) ;
  </code></pre>
  
  <p>In the required syntax the selections would be based on sum(group_count) - which will be the total of all group counts. How would I get the same in postgressql , more specifically in Redshift.
  Note that <strong>create function is not supported in Redshift</strong>. Also prepare &amp; set is available but not prepare statement in Redshift. </p>
  
  <pre><code>   --sample output set---
      Head_group core_group tail_group 
      A           D          F
      B           E
      C
      --Alternative sample output set---
      Head_group 
      A
      B
      C
      core_group 
      D
      E
      tail_group 
      F
  </code></pre>
  
  <p>Note that each group can have a different number of rows returned. 
  in <strong>mysql</strong>, I could do something like-:</p>
  
  <pre><code>set @total_group_count =(select count(*) from mytable ) ;
  set @percentile_group_count = ( select @total_group_count*(30/100))  ; 
  </code></pre>
  
  <p>Reference to my related question : 
  <a href="https://stackoverflow.com/questions/21310085/storing-the-results-of-a-prepared-statement-as-a-table-in-mysql">Storing the results of a prepared statement as a table in mysql?</a></p>