-Re: subquery in select
wd 2012-05-28, 04:07
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
> From data
> Group by category
> Thank You,
> On Fri, May 25, 2012 at 8:56 PM, Edward Capriolo <[EMAIL PROTECTED]>
>> 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.
>> 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