hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ashish Thusoo <>
Subject RE: any way to check the sum in where clause
Date Fri, 10 Apr 2009 02:20:04 GMT
We haven't really priotized this because of the subquery workaround but we should have a JIRA
around it anyway. Lately, we have been spending a lot of time on bug fixes and performance


From: Jeff Hammerbacher []
Sent: Thursday, April 09, 2009 6:54 PM
Subject: Re: any way to check the sum in where clause

Hey Hive team,

Has there been any discussion of supporting HAVING rather than requiring a manual query rewrite
in the future? Curious to hear if it's a priority.


On Wed, Apr 8, 2009 at 9:03 PM, Prasad Chakka <<>>

Hi Frederick,

Select sum(col1), key from tab1 group by key having sum(col1) > 5

Can be written in Hive as

Select a.*
>From (select sum(col1) as s, key from tab1 group by key) a
Where a.s > 5


From: Frederick Oko <<>>
Reply-To: <<>>
Date: Wed, 8 Apr 2009 19:43:13 -0700
To: <<>>
Subject: Re: any way to check the sum in where clause

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" <<>>


  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?


View raw message