Hi (vers),

This might be a very basic question for most of you but I am stuck at it for quite some time now. I have a table with three columns :
Describe usage;
ts string
id string
metric double

I am trying to do a query like
Select ts,id,sum(metric/(select count(*) from usage)) from usage group by ts,id;

This throws a parse error- Can't recognize input near 'select'  'count'  '(' in expression specification.
I tried setting the output in a temp variable and use it in the query like
Set totalrows = select count(*) from usage;
Select ts, id, sum(metric/${hiveconf:totalrows}) from usage group by ts,id;

This also throws a parse error as the variable gets substituted by variable. So I have three questions.

1.       What is wrong with the above queries?

2.       Is there another way to find number of rows in a table?

3.       Is there a better way for what I am trying to do?

Thanks,
Richin

This should work

Select ts,id,sum(metric/usage_count) from usage join (select count(*) usage_count from usage) V on ( 1 = 1) group by ts,id;

thanks,
Shrikanth

On Aug 9, 2012, at 1:33 PM, <[EMAIL PROTECTED]> wrote:

> Hi (vers),
>  
> This might be a very basic question for most of you but I am stuck at it for quite some time now. I have a table with three columns :            
> Describe usage;
> ts string
> id string
> metric double
>  
> I am trying to do a query like
> Select ts,id,sum(metric/(select count(*) from usage)) from usage group by ts,id;
>  
> This throws a parse error- Can’t recognize input near ‘select’  ‘count’  ‘(‘ in expression specification.
> I tried setting the output in a temp variable and use it in the query like
> Set totalrows = select count(*) from usage;
> Select ts, id, sum(metric/${hiveconf:totalrows}) from usage group by ts,id;
>  
> This also throws a parse error as the variable gets substituted by variable. So I have three questions.
> 1.       What is wrong with the above queries?
> 2.       Is there another way to find number of rows in a table?
> 3.       Is there a better way for what I am trying to do?
>  
> Thanks,
> Richin

Basically a cross join. You would have the same issue with SQL.

Bertrand

On Thu, Aug 9, 2012 at 10:41 PM, shrikanth shankar <[EMAIL PROTECTED]>wrote:

> This should work
>
> Select ts,id,sum(metric/usage_count) from usage join (select count(*)
> usage_count from usage) V on ( 1 = 1) group by ts,id;
>
> thanks,
> Shrikanth
>
> On Aug 9, 2012, at 1:33 PM, <[EMAIL PROTECTED]> wrote:
>
> Hi (vers),****
> ** **
> This might be a very basic question for most of you but I am stuck at it
> for quite some time now. I have a table with three columns :            **
> **
> *Describe usage;*
> *ts string*
> *id string*
> *metric double*
> ** **
> I am trying to do a query like****
> *Select ts,id,sum(metric/(select count(*) from usage)) from usage group
> by ts,id;*
> ** **
> This throws a parse error- Can’t recognize input near ‘select’  ‘count’
>  ‘(‘ in expression specification.****
> I tried setting the output in a temp variable and use it in the query like
> ****
> *Set totalrows = select count(*) from usage;*
> *Select ts, id, sum(metric/${hiveconf:totalrows}) from usage group by
> ts,id;*
> ** **
> This also throws a parse error as the variable gets substituted by
> variable. So I have three questions.****
> 1.       What is wrong with the above queries?****
> 2.       Is there another way to find number of rows in a table?****
> 3.       Is there a better way for what I am trying to do?****
> ** **
> Thanks,****
> Richin****
>
>
>
--
Bertrand Dechoux

Thanks Guys, it worked.

From: ext Bertrand Dechoux [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 09, 2012 5:03 PM
To: [EMAIL PROTECTED]
Subject: Re: Nested Select Statements

Basically a cross join. You would have the same issue with SQL.

Bertrand
On Thu, Aug 9, 2012 at 10:41 PM, shrikanth shankar <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:
This should work

Select ts,id,sum(metric/usage_count) from usage join (select count(*) usage_count from usage) V on ( 1 = 1) group by ts,id;

thanks,
Shrikanth

On Aug 9, 2012, at 1:33 PM, <[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>> wrote:
Hi (vers),

This might be a very basic question for most of you but I am stuck at it for quite some time now. I have a table with three columns :
Describe usage;
ts string
id string
metric double

I am trying to do a query like
Select ts,id,sum(metric/(select count(*) from usage)) from usage group by ts,id;

This throws a parse error- Can't recognize input near 'select'  'count'  '(' in expression specification.
I tried setting the output in a temp variable and use it in the query like
Set totalrows = select count(*) from usage;
Select ts, id, sum(metric/${hiveconf:totalrows}) from usage group by ts,id;

This also throws a parse error as the variable gets substituted by variable. So I have three questions.
1.       What is wrong with the above queries?
2.       Is there another way to find number of rows in a table?
3.       Is there a better way for what I am trying to do?

Thanks,
Richin
--
Bertrand Dechoux

I don't have a hive shell under my hands. What's the planning?

It should be doable with a single MapReduce if the cross join is
constructed as a 'HashJoin'.
Is that the case? I am curious.

(You can use 'explain' on your query to know the planning.)

Bertrand

On Thu, Aug 9, 2012 at 11:08 PM, <[EMAIL PROTECTED]> wrote:

>  Thanks Guys, it worked.****
>
> ** **
>
> *From:* ext Bertrand Dechoux [mailto:[EMAIL PROTECTED]]
> *Sent:* Thursday, August 09, 2012 5:03 PM
> *To:* [EMAIL PROTECTED]
> *Subject:* Re: Nested Select Statements****
>
> ** **
>
> Basically a cross join. You would have the same issue with SQL.
>
> Bertrand****
>
> On Thu, Aug 9, 2012 at 10:41 PM, shrikanth shankar <[EMAIL PROTECTED]>
> wrote:****
>
> This should work****
>
> ** **
>
> Select ts,id,sum(metric/usage_count) from usage join (select count(*)
> usage_count from usage) V on ( 1 = 1) group by ts,id;****
>
> ** **
>
> thanks,****
>
> Shrikanth****
>
> ** **
>
> On Aug 9, 2012, at 1:33 PM, <[EMAIL PROTECTED]> wrote:****
>
>
>
> ****
>
> Hi (vers),****
>
>  ****
>
> This might be a very basic question for most of you but I am stuck at it
> for quite some time now. I have a table with three columns :            **
> **
>
> *Describe usage;*****
>
> *ts string*****
>
> *id string*****
>
> *metric double*****
>
>  ****
>
> I am trying to do a query like****
>
> *Select ts,id,sum(metric/(select count(*) from usage)) from usage group
> by ts,id;*****
>
>  ****
>
> This throws a parse error- Can’t recognize input near ‘select’  ‘count’
>  ‘(‘ in expression specification.****
>
> I tried setting the output in a temp variable and use it in the query like
> ****
>
> *Set totalrows = select count(*) from usage;*****
>
> *Select ts, id, sum(metric/${hiveconf:totalrows}) from usage group by
> ts,id;*****
>
>  ****
>
> This also throws a parse error as the variable gets substituted by
> variable. So I have three questions.****
>
> 1.       What is wrong with the above queries?****
>
> 2.       Is there another way to find number of rows in a table?****
>
> 3.       Is there a better way for what I am trying to do?****
>
>  ****
>
> Thanks,****
>
> Richin****
>
> ** **
>
>
>
>
> --
> Bertrand Dechoux****
>

--
Bertrand Dechoux