Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: pass (nike.apache.org: domain of john.meagher@gmail.com
 designates 209.85.223.176 as permitted sender)
MIME-Version: 1.0
In-Reply-To: 
 <CAOrJPzcdb6+30+FxHrzGbHrdtEwqrRWnzvOW-ni21WTGyS34tA@mail.gmail.com>
References: 
 <CAOrJPzcdb6+30+FxHrzGbHrdtEwqrRWnzvOW-ni21WTGyS34tA@mail.gmail.com>
Date: Tue, 30 Oct 2012 09:57:41 -0400
Message-ID: 
 <CAKb7DdgqiuoXT5UGSP02C0kjp=6y=74C8mb93hPHJxuiSnpW5Q@mail.gmail.com>
Subject: Re: Help in hive query
From: John Meagher <john.meagher@gmail.com>
To: user@hive.apache.org
Content-Type: text/plain; charset=ISO-8859-1

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.hive04@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