title: Count and group by in Hive
body: <p>I have a table in the hive as follows,</p>
  
  <pre><code>table1
  
  Cola   | Colb  |  Colc |  Cold  |
  ---------------------------------
  ...etc
  efo18   691 123 5692                                 
  efo18   691 345 5657
  ...etc
  fsx31   950 291 23456                                                         
  fsx31   950 404 23456                                                          
  fsx31   950 343 23456                                                         
  fsx31   950 182 23456                                                         
  fsx31   950 120 45042                                                         
  fsx31   950 161 23456  
  ....etc
  klz57   490 121 3330                                                          
  klz57   490 113 3330                                                          
  klz57   490 308 3330                                                          
  klz57   490 411 3330                                                           
  klz57   490 161 3330                                                          
  klz57   386 108 3330                                                          
  klz57   490 113 3330                                                          
  klz57   490 125 3330                                                          
  klz57   490 165 3330                                                          
  klz57   490 166 3330  
  ...etc
  ---------------------------------
  </code></pre>
  
  <p>I wanted another table from <code>table1</code> data with those having 
  same value for <code>Cold</code> in a group
      and within that, those having the same <code>Colb</code> to have a sub-group
          and within that sub-group, those havin same values of <code>Cola</code> to belong to a group.
  In other words, each unique combination of <code>Cola,Colb,Cold</code> to be a row.And repeated rows be summed up.</p>
  
  <p><code>insert into table table2(Col1 string,Col2 string,Col3 string,Count int) select cola,colb,cold,count(*) from table1 group by cold,colb,cola;</code></p>
  
  <p>I expected this,</p>
  
  <pre><code>Col1   | Col2  |  Col3     |  Count  |
  -------------------------------------
  efo18    691     5692         1
  efo18    691     5657         1
  fsx31    950     23456        5   &lt;-----1
  fsx31    950     45042        1   &lt;-----1
  klz57    490     1234         9   &lt;-----2
  klz57    386     1234         1   &lt;-----2
  --------------------------------------
  </code></pre>
  
  <p>I got this,</p>
  
  <pre><code>table2
  
  Col1   | Col2  |  Col3     |  Count  |
  -------------------------------------
  efo18    691     5692         1
  efo18    691     5657         1
  fsx31    950     23456        4   &lt;-----1
  fsx31    950     25456        1   &lt;-----1
  fsx31    950     45042        1   &lt;-----1
  klz57    490     1234         8   &lt;-----2
  klz57    386     1234         1   &lt;-----2
  klz57    490     1234         1   &lt;-----2
  --------------------------------------
  </code></pre>
  
  <p>What I don't understand is I am doing a group by on <code>Cold</code> then on <code>Colb</code> followed by <code>Cola</code>, then 
  why does the <code>Count</code> for the rows marked (&lt;----1),for values from <code>Cola</code>, is in different rows, even though everything falls in the same group? 
  <code>Colc</code> is different for these two rows, but I haven't made use of it in grouping sp how are the two rows different?.
  Similarly for rows marked (&lt;----2), what is the problem here.</p>
  
  <p><strong>Update:</strong></p>
  
  <p>Binary01, I was trying out the example you gave </p>
  
  <pre><code>hive&gt; select * from xyz;
  OK
  x        y       z      zz
  xxx     111     222     123 NULL    NULL    NULL
  xxx     111     222     123 NULL    NULL    NULL
  xxx     101     222     123 NULL    NULL    NULL
  xux     111     422     123 NULL    NULL    NULL
  xxx     111     522     323 NULL    NULL    NULL
  xyx     111     622     123 NULL    NULL    NULL
  xxx     115     322     123 NULL    NULL    NULL
  xxx     111     122     123 NULL    NULL    NULL
  xxx     111     223     123 NULL    NULL    NULL
  xxy     111     212     143 NULL    NULL    NULL
  xxx     117     222     123 NULL    NULL    NULL
  </code></pre>
  
  <p>What are those NULL values doing there? I have copy pasted your example line by line. Even creating the table as , </p>
  
  <pre><code>create table xyz(x string ,y string, z string , zz string) 
  row format delimited fields terminated by ',';
  </code></pre>
  
  <p>and the final query gives,</p>
  
  <pre><code>hive&gt; select * from xyztemp;
  OK
  xux     111     422     123 NULL    NULL    1
  xxx     101     222     123 NULL    NULL    1
  xxx     111     122     123 NULL    NULL    1
  xxx     111     222     123 NULL    NULL    2
  xxx     111     223     123 NULL    NULL    1
  xxx     111     522     323 NULL    NULL    1
  xxx     115     322     123 NULL    NULL    1
  xxx     117     222     123 NULL    NULL    1
  xxy     111     212     143 NULL    NULL    1
  xyx     111     622     123 NULL    NULL    1
  </code></pre>