How to do select based on a substring of a particular column in HiveQL and SQL?

Question

I have a table in hive

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    

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.

Select code, minutes as total  
from TableT   
where S.no > 1
group by code 
order by total

Edit: The ranked results based on first 4 digits of zipcode should look something like this

total code

322(i.e 104+114+104) 7518
154(i.e 20+114+20) 7508
124 7510

Answer 1

Hmmm. I'm thinking you want this:

select substr(zip, 1, 4) as region, sum(minutes) as minutes,
       rank() over (order by sum(minutes) desc) as therank
from tableT
where s.no > 1
group by substr(zip, 1, 4)
order by minutes desc;