Thanks Guys, it worked. From: ext Bertrand Dechoux [mailto:decho...@gmail.com] Sent: Thursday, August 09, 2012 5:03 PM To: user@hive.apache.org Subject: Re: Nested Select Statements
Basically a cross join. You would have the same issue with SQL. Bertrand On Thu, Aug 9, 2012 at 10:41 PM, shrikanth shankar <sshan...@qubole.com<mailto:sshan...@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.j...@nokia.com<mailto:richin.j...@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
