Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - Nested Select Statements


Copy link to this message
-
Re: Nested Select Statements
shrikanth shankar 2012-08-09, 20:41
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, <[EMAIL PROTECTED]> 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