You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by shan s <my...@gmail.com> on 2012/05/28 15:48:09 UTC
Need help with simple subquery
I need help with a simple subquery. Given below data, I need counts and
percentage counts per category. (Re-phrasing my earlier question )
With the code below I get an error: *FAILED: Parse Error:* line 6:50
*mismatched
input ','* *expecting EOF near 'a'*
Looking at the documentation the syntax it is not clear to me.. In the form
clause, can I have multiple selects and just alias them and use them in the
top query?
If so, what am I missing in the below code...
select a.category, a.count, b.totalCount
from
(select category, count(*) as count from gt group by category) a,
(select count(*) as totalCount from gt) b ;
Many Thanks,
Prashant.
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
Re: Need help with simple subquery
Posted by shan s <my...@gmail.com>.
Thanks Nanda, Igor,
This worked..
On Tue, May 29, 2012 at 11:55 PM, Nanda Vijaydev
<na...@gmail.com>wrote:
> Use a join as given below, I have used the table name temp.
>
> select a.Category, a.count, b.tot_count, (100*a.count)/tot_count as
> percent_count
> from ( select Category, count(*) as count from temp group by Category )
> a
> join (select count(*) as tot_count from temp) b
> on (true)
>
> Hope this helps
> Nanda Vijaydev
>
>
> On Mon, May 28, 2012 at 3:28 PM, Igor Tatarinov <ig...@decide.com> wrote:
>
>> Try replacing the comma with JOIN
>>
>> igor
>> decide.com
>>
>> On Mon, May 28, 2012 at 6:48 AM, shan s <my...@gmail.com> wrote:
>>
>>> I need help with a simple subquery. Given below data, I need counts and
>>> percentage counts per category. (Re-phrasing my earlier question )
>>> With the code below I get an error: *FAILED: Parse Error:* line 6:50 *mismatched
>>> input ','* *expecting EOF near 'a'*
>>>
>>> Looking at the documentation the syntax it is not clear to me.. In the
>>> form clause, can I have multiple selects and just alias them and use them
>>> in the top query?
>>> If so, what am I missing in the below code...
>>>
>>> select a.category, a.count, b.totalCount
>>> from
>>> (select category, count(*) as count from gt group by category) a,
>>> (select count(*) as totalCount from gt) b ;
>>>
>>> Many Thanks,
>>> Prashant.
>>>
>>> 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
>>>
>>
>>
>
Re: Need help with simple subquery
Posted by Nanda Vijaydev <na...@gmail.com>.
Use a join as given below, I have used the table name temp.
select a.Category, a.count, b.tot_count, (100*a.count)/tot_count as
percent_count
from ( select Category, count(*) as count from temp group by Category ) a
join (select count(*) as tot_count from temp) b
on (true)
Hope this helps
Nanda Vijaydev
On Mon, May 28, 2012 at 3:28 PM, Igor Tatarinov <ig...@decide.com> wrote:
> Try replacing the comma with JOIN
>
> igor
> decide.com
>
> On Mon, May 28, 2012 at 6:48 AM, shan s <my...@gmail.com> wrote:
>
>> I need help with a simple subquery. Given below data, I need counts and
>> percentage counts per category. (Re-phrasing my earlier question )
>> With the code below I get an error: *FAILED: Parse Error:* line 6:50 *mismatched
>> input ','* *expecting EOF near 'a'*
>>
>> Looking at the documentation the syntax it is not clear to me.. In the
>> form clause, can I have multiple selects and just alias them and use them
>> in the top query?
>> If so, what am I missing in the below code...
>>
>> select a.category, a.count, b.totalCount
>> from
>> (select category, count(*) as count from gt group by category) a,
>> (select count(*) as totalCount from gt) b ;
>>
>> Many Thanks,
>> Prashant.
>>
>> 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
>>
>
>
Re: Need help with simple subquery
Posted by Igor Tatarinov <ig...@decide.com>.
Try replacing the comma with JOIN
igor
decide.com
On Mon, May 28, 2012 at 6:48 AM, shan s <my...@gmail.com> wrote:
> I need help with a simple subquery. Given below data, I need counts and
> percentage counts per category. (Re-phrasing my earlier question )
> With the code below I get an error: *FAILED: Parse Error:* line 6:50 *mismatched
> input ','* *expecting EOF near 'a'*
>
> Looking at the documentation the syntax it is not clear to me.. In the
> form clause, can I have multiple selects and just alias them and use them
> in the top query?
> If so, what am I missing in the below code...
>
> select a.category, a.count, b.totalCount
> from
> (select category, count(*) as count from gt group by category) a,
> (select count(*) as totalCount from gt) b ;
>
> Many Thanks,
> Prashant.
>
> 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
>