Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive >> mail # user >> Nested Select Statements


+
richin.jain@... 2012-08-09, 20:33
Copy link to this message
-
Re: Nested Select Statements
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

+
Bertrand Dechoux 2012-08-09, 21:02
+
richin.jain@... 2012-08-09, 21:08
+
Bertrand Dechoux 2012-08-09, 21:19
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB