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
