Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
>
>
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB