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 > >
