Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> subquery in select


Copy link to this message
-
Re: subquery in select
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
>
>