I transled it to below, but I am getting an error. FAILED: Parse Error: line 6:50 mismatched input ',' expecting EOF near 'a'
select a.category, a.count, a.count/b.totalCount as percentageValue from (select category, count(*) as count from gt group by cat) a, (select count(*) as totalCount from gt) b ; Thanks for your help! On Mon, May 28, 2012 at 9:37 AM, wd <[email protected]> wrote: > select a.id, a.count, a.count/b.val > from (select id, count(*) as count from data group by id) a, > (select val from tableY ....) b > > You don't have a join condition, this maybe output very large data. > > On Mon, May 28, 2012 at 11:45 AM, shan s <[email protected]> wrote: > > Thanks Edward. But I didn't get the trick yet. > > I was able to use FROM with multiple group bys. But failed to see what to > > replace the subquery with... > > > > Could you please give an example for my use case below. > > > > Select id, count(*), count(*)/ (select val from tableY where name like > > ‘xyzdivisor’) > > From data > > Group by category > > > > Thank You, > > Prashant. > > On Fri, May 25, 2012 at 8:56 PM, Edward Capriolo <[email protected]> > > wrote: > >> > >> No. But hive does support nested selects so must queries can be > >> re-written to accomplish the same thing. Actually I love hives > >> > >> "FROM ( ) SELECT" syntax > >> > >> It takes a while to get used to but it is much more clear then SQL > >> standard switch allows supqueries in all kinds of places and has about > >> 40 difference ways to express the same query. > >> > >> https://cwiki.apache.org/Hive/languagemanual.html > >> > >> On Fri, May 25, 2012 at 11:14 AM, shan s <[email protected]> wrote: > >> > Hi All, > >> > Does hive support subquery in select statement? > >> > > >> > Given below data, I need counts and percentage counts per category. > The > >> > divisor in my case is not the total count, but something that is > stored > >> > in > >> > another table.. In T-SQL, I can do subquery in select statement to get > >> > my > >> > divisor. > >> > > >> > Select id, count(*), count(*)/ (select val from tableY where name like > >> > ‘xyzdivisor’) > >> > From data > >> > Group by category > >> > > >> > What am I missing? This looks like a simple case.. > >> > > >> > Id Category Count Output Percentage Output > >> > 1 A A 4 A 40 (4/10)*100 > >> > 2 A B 3 B 30 > >> > 3 B C 3 C 30 > >> > 4 A > >> > 5 C > >> > 6 C > >> > 7 B > >> > 8 B > >> > 9 A > >> > 10 C > >> > Thanks, Prashant > > > > >
