title: How to do select based on a substring of a particular column in HiveQL and SQL?
body: <p>I have a table in hive</p>
  
  <pre><code>S.no  Age  minutes  code  
   1     10   20     75081     
   2     11   114    75080      
   3     21   104    75180     
   4     31   124    75108    
   5     10   20     75083     
   6     11   114    75180    
   7     21   104    75180    
  </code></pre>
  
  <p>I want to write an hivesql/sql query that gives ranked list of total minutes spoken based on the region i.e first 4 digits of code.
  How should i go about it? I know that SUBSTRING() gives me the required cut, but I have not been able to go from there.</p>
  
  <pre><code>Select code, minutes as total  
  from TableT   
  where S.no &gt; 1
  group by code 
  order by total
  </code></pre>
  
  <p>Edit: 
  The ranked results based on first 4 digits of zipcode should look something like this </p>
  
  <p>total  code</p>
  
  <p>322(i.e 104+114+104)   7518<br>
    154(i.e 20+114+20) 7508<br>
  124    7510 </p>