Hey john, I did the same using join from two tables but your suggestion helped me a lot in many ways.It works great!
Thanks a ton! Regards, dti On Tue, Oct 30, 2012 at 7:27 PM, John Meagher <john.meag...@gmail.com>wrote: > The WHERE part in the approvals can be moved up to be an IF in the > SELECT... > > > SELECT client_id,receive_dd,receive_hh, > receive_hh+1, > COUNT(1) AS transaction_count, > SUM( IF ( response=00, 1, 0) ) AS approval_count, > SUM( IF ( response=00, 1, 0) ) / COUNT(1) * 100 AS percent > FROM sale_test > group by fdc_client_id,receive_dd,receive_hh,receive_hh+1; > > > > On Tue, Oct 30, 2012 at 1:51 AM, dyuti a <hadoop.hiv...@gmail.com> wrote: > > Hi All, > > > > I want to perform (No.of .approvals in an hour/No.of transactions in that > > hour)*100. > > > > //COUNT(1) AS cnt gives total transactions in an hour > > SELECT client_id,receive_dd,receive_hh,receive_hh+1,COUNT(1) AS cnt FROM > > sale_test group by fdc_client_id,receive_dd,receive_hh,receive_hh+1; > > > > GETREGREOS 23 16 17 5969 > > GETREGREOS 23 21 22 2602 > > GETREGREOS 24 3 4 114 > > > > //Approved transactions where response=00 > > SELECT client_id,receive_dd,receive_hh,receive_hh+1,COUNT(1) AS cnt FROM > > sale_test where response=00 group by > > client_id,receive_dd,receive_hh,receive_hh+1; > > GETREGREOS 23 16 17 5775 > > GETREGREOS 23 21 22 2515 > > GETREGREOS 24 3 4 103 > > > > > > I want to perform 100 * (5775/5969) , 100 * (2515/2602) , 100 * (103/114) > > like the same for all other clients for each hour i.e., (No.of > .approvals in > > an hour/No.of transactions in that hour)*100. > > > > Please help me out as how to achieve this in hive. > > > > > > Thanks & Regards, > > dti >
