You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@hive.apache.org by Manu A <ha...@gmail.com> on 2012/10/10 08:19:27 UTC

Help in hive query

 Hi All,
The result for the below query is 194965.0        0.0 , but 194965 is the
result of inner query from count(q1.response). It looks like the outer
query [select avg(q2.auth_count), stddev_pop(q2.auth_count)]didn't work at
all.


 //Query
select avg(q2.auth_count), stddev_pop(q2.auth_count)
from (
        select q1.TEXT_CCYY ,count(q1.response) as auth_count
        from(
               select * from Sale1 where TEXT_DD=7 AND TEXT_HH=15  AND
response=00)q1
        group by q1.TEXT_CCYY,q1.response)q2
group by q2.auth_count;


Please help me is there anything i have to change in query.


*Thanks & Regards,*
*Manu*

Re: Help in hive query

Posted by Manu A <ha...@gmail.com>.

Thanks Jan. It worked!


Regards,
Manu

On Wed, Oct 10, 2012 at 12:00 PM, Jan Dolinár <do...@gmail.com> wrote:

> Hi Manu,
>
> I believe the last "group by q2.auth_count" is wrong, because it
> causes computing average only across lines with same value of
> q2.auth_count, which is of course equal to its value.
>
> Best regards,
> J. Dolinar
>
> On Wed, Oct 10, 2012 at 8:19 AM, Manu A <ha...@gmail.com> wrote:
> > Hi All,
> > The result for the below query is 194965.0        0.0 , but 194965 is the
> > result of inner query from count(q1.response). It looks like the outer
> query
> > [select avg(q2.auth_count), stddev_pop(q2.auth_count)]didn't work at all.
> >
> >
> >  //Query
> > select avg(q2.auth_count), stddev_pop(q2.auth_count)
> > from (
> >         select q1.TEXT_CCYY ,count(q1.response) as auth_count
> >         from(
> >                select * from Sale1 where TEXT_DD=7 AND TEXT_HH=15  AND
> > response=00)q1
> >         group by q1.TEXT_CCYY,q1.response)q2
> > group by q2.auth_count;
> >
> >
> > Please help me is there anything i have to change in query.
> >
> >
> > Thanks & Regards,
> > Manu
> >
> >
>

Re: Help in hive query

Posted by Jan Dolinár <do...@gmail.com>.

Hi Manu,

I believe the last "group by q2.auth_count" is wrong, because it
causes computing average only across lines with same value of
q2.auth_count, which is of course equal to its value.

Best regards,
J. Dolinar

On Wed, Oct 10, 2012 at 8:19 AM, Manu A <ha...@gmail.com> wrote:
> Hi All,
> The result for the below query is 194965.0        0.0 , but 194965 is the
> result of inner query from count(q1.response). It looks like the outer query
> [select avg(q2.auth_count), stddev_pop(q2.auth_count)]didn't work at all.
>
>
>  //Query
> select avg(q2.auth_count), stddev_pop(q2.auth_count)
> from (
>         select q1.TEXT_CCYY ,count(q1.response) as auth_count
>         from(
>                select * from Sale1 where TEXT_DD=7 AND TEXT_HH=15  AND
> response=00)q1
>         group by q1.TEXT_CCYY,q1.response)q2
> group by q2.auth_count;
>
>
> Please help me is there anything i have to change in query.
>
>
> Thanks & Regards,
> Manu
>
>