hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bertrand Dechoux <decho...@gmail.com>
Subject Re: Nested Select Statements
Date Thu, 09 Aug 2012 21:02:52 GMT
Basically a cross join. You would have the same issue with SQL.

Bertrand

On Thu, Aug 9, 2012 at 10:41 PM, shrikanth shankar <sshankar@qubole.com>wrote:

> This should work
>
> Select ts,id,sum(metric/usage_count) from usage join (select count(*)
> usage_count from usage) V on ( 1 = 1) group by ts,id;
>
> thanks,
> Shrikanth
>
> On Aug 9, 2012, at 1:33 PM, <richin.jain@nokia.com> wrote:
>
> Hi (vers),****
> ** **
> This might be a very basic question for most of you but I am stuck at it
> for quite some time now. I have a table with three columns :            **
> **
> *Describe usage;*
> *ts string*
> *id string*
> *metric double*
> ** **
> I am trying to do a query like****
> *Select ts,id,sum(metric/(select count(*) from usage)) from usage group
> by ts,id;*
> ** **
> This throws a parse error- Can’t recognize input near ‘select’  ‘count’
>  ‘(‘ in expression specification.****
> I tried setting the output in a temp variable and use it in the query like
> ****
> *Set totalrows = select count(*) from usage;*
> *Select ts, id, sum(metric/${hiveconf:totalrows}) from usage group by
> ts,id;*
> ** **
> This also throws a parse error as the variable gets substituted by
> variable. So I have three questions.****
> 1.       What is wrong with the above queries?****
> 2.       Is there another way to find number of rows in a table?****
> 3.       Is there a better way for what I am trying to do?****
> ** **
> Thanks,****
> Richin****
>
>
>


-- 
Bertrand Dechoux

Mime
View raw message