You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Ravisankar Mani <rr...@gmail.com> on 2015/06/03 12:35:29 UTC

Group by query

Hi everyone,

       I have selected specific column using aggregation .    " FAILED:
SemanticException [Error 10002]: Line 2:26 Invalid column reference
'contactid " .  Exception occur while executing following query. But it
working in sql server without using group by. Kindly refer the query.


SELECT
  adventurepersoncontacts.contactid as contactid
 ,adventurepersoncontacts.fullname as fullname
 ,adventurepersoncontacts.age as age
 ,adventurepersoncontacts.emailaddress as emailaddress
 ,adventurepersoncontacts.phoneno as phoneno
 ,adventurepersoncontacts.modifieddate as modifieddate
, COUNT(adventurepersoncontacts.contactid)  as contact
 FROM
 default.adventurepersoncontacts

Regards,
Ravisankar M R

Re: Group by query

Posted by Ravisankar Mani <rr...@gmail.com>.
Hi Bhagwan,

             Thanks for your response...

           Is there any possible to use aggregated and non aggregated
columns in hive query to get data without using group by?


Regards,
Ravi





On Wed, Jun 3, 2015 at 10:24 AM, Bhagwan S. Soni <bh...@gmail.com>
wrote:

> Ravishankar,
>
> below query will works fine without including group by
> SELECT
>  COUNT(adventurepersoncontacts.contactid)  as contact
>  FROM
>  default.adventurepersoncontacts
>
> But when you are planning to get aggregated column with non-aggregated
> column you have to specify the level of aggregation.
> Why don't you include selected column in group by, I don't think so it
> will affect your output.
>
> Thanks,
> Bhagwan
>
> On Wed, Jun 3, 2015 at 4:05 PM, Ravisankar Mani <rr...@gmail.com> wrote:
>
>> Hi everyone,
>>
>>        I have selected specific column using aggregation .    " FAILED:
>> SemanticException [Error 10002]: Line 2:26 Invalid column reference
>> 'contactid " .  Exception occur while executing following query. But it
>> working in sql server without using group by. Kindly refer the query.
>>
>>
>> SELECT
>>   adventurepersoncontacts.contactid as contactid
>>  ,adventurepersoncontacts.fullname as fullname
>>  ,adventurepersoncontacts.age as age
>>  ,adventurepersoncontacts.emailaddress as emailaddress
>>  ,adventurepersoncontacts.phoneno as phoneno
>>  ,adventurepersoncontacts.modifieddate as modifieddate
>> , COUNT(adventurepersoncontacts.contactid)  as contact
>>  FROM
>>  default.adventurepersoncontacts
>>
>> Regards,
>> Ravisankar M R
>>
>
>

Re: Group by query

Posted by "Bhagwan S. Soni" <bh...@gmail.com>.
Ravishankar,

below query will works fine without including group by
SELECT
 COUNT(adventurepersoncontacts.contactid)  as contact
 FROM
 default.adventurepersoncontacts

But when you are planning to get aggregated column with non-aggregated
column you have to specify the level of aggregation.
Why don't you include selected column in group by, I don't think so it will
affect your output.

Thanks,
Bhagwan

On Wed, Jun 3, 2015 at 4:05 PM, Ravisankar Mani <rr...@gmail.com> wrote:

> Hi everyone,
>
>        I have selected specific column using aggregation .    " FAILED:
> SemanticException [Error 10002]: Line 2:26 Invalid column reference
> 'contactid " .  Exception occur while executing following query. But it
> working in sql server without using group by. Kindly refer the query.
>
>
> SELECT
>   adventurepersoncontacts.contactid as contactid
>  ,adventurepersoncontacts.fullname as fullname
>  ,adventurepersoncontacts.age as age
>  ,adventurepersoncontacts.emailaddress as emailaddress
>  ,adventurepersoncontacts.phoneno as phoneno
>  ,adventurepersoncontacts.modifieddate as modifieddate
> , COUNT(adventurepersoncontacts.contactid)  as contact
>  FROM
>  default.adventurepersoncontacts
>
> Regards,
> Ravisankar M R
>