You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by javateck javateck <ja...@gmail.com> on 2009/04/09 04:14:43 UTC

any way to check the sum in where clause

Hi,
  I have a query like, select sum(col1) as s from tab1 where s>5 (this will
not be working in SQL either), what I need is to filter the result so that I
only get results whose sum is > 5. Anyway to do it in one query?

thanks,

Re: any way to check the sum in where clause

Posted by Namit Jain <nj...@facebook.com>.
You can use a subquery for the same:

select * from
(select col1, sum(col2) as s from tab1 group by col1) x
where x.s >100


On 4/8/09 8:00 PM, "javateck javateck" <ja...@gmail.com> wrote:

right, it would be something like
select col1, sum(col2) as s from tab1 group by col1 having sum(col2)>100 sort by col1

On Wed, Apr 8, 2009 at 7:43 PM, Frederick Oko <fr...@gmail.com> wrote:

Assuming the result is less trivial than you indicate by your example such that you would have a GROUP BY clause SQL provides the HAVING clause to filter based on an aggregate in a single query. Even in the first release docs the absence of this in Hive was a noted caveat and I don't see a Jira for it or find it in the grammar.

On Apr 8, 2009 7:15 PM, "javateck javateck" <ja...@gmail.com> wrote:

Hi,

  I have a query like, select sum(col1) as s from tab1 where s>5 (this will not be working in SQL either), what I need is to filter the result so that I only get results whose sum is > 5. Anyway to do it in one query?

thanks,

Re: any way to check the sum in where clause

Posted by javateck javateck <ja...@gmail.com>.
right, it would be something likeselect col1, sum(col2) as s from tab1 group
by col1 having sum(col2)>100 sort by col1

On Wed, Apr 8, 2009 at 7:43 PM, Frederick Oko <fr...@gmail.com>wrote:

> Assuming the result is less trivial than you indicate by your example such
> that you would have a GROUP BY clause SQL provides the HAVING clause to
> filter based on an aggregate in a single query. Even in the first release
> docs the absence of this in Hive was a noted caveat and I don't see a Jira
> for it or find it in the grammar.
>
> On Apr 8, 2009 7:15 PM, "javateck javateck" <ja...@gmail.com> wrote:
>
> Hi,
>   I have a query like, select sum(col1) as s from tab1 where s>5 (this will
> not be working in SQL either), what I need is to filter the result so that I
> only get results whose sum is > 5. Anyway to do it in one query?
>
> thanks,
>
>

Re: any way to check the sum in where clause

Posted by Frederick Oko <fr...@gmail.com>.
Assuming the result is less trivial than you indicate by your example such
that you would have a GROUP BY clause SQL provides the HAVING clause to
filter based on an aggregate in a single query. Even in the first release
docs the absence of this in Hive was a noted caveat and I don't see a Jira
for it or find it in the grammar.

On Apr 8, 2009 7:15 PM, "javateck javateck" <ja...@gmail.com> wrote:

Hi,
  I have a query like, select sum(col1) as s from tab1 where s>5 (this will
not be working in SQL either), what I need is to filter the result so that I
only get results whose sum is > 5. Anyway to do it in one query?

thanks,