Return-Path: Delivered-To: apmail-hadoop-hive-user-archive@minotaur.apache.org Received: (qmail 46920 invoked from network); 1 Apr 2009 19:41:10 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 1 Apr 2009 19:41:10 -0000 Received: (qmail 42052 invoked by uid 500); 1 Apr 2009 19:41:10 -0000 Delivered-To: apmail-hadoop-hive-user-archive@hadoop.apache.org Received: (qmail 41979 invoked by uid 500); 1 Apr 2009 19:41:09 -0000 Mailing-List: contact hive-user-help@hadoop.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: hive-user@hadoop.apache.org Delivered-To: mailing list hive-user@hadoop.apache.org Received: (qmail 41969 invoked by uid 99); 1 Apr 2009 19:41:09 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 01 Apr 2009 19:41:09 +0000 X-ASF-Spam-Status: No, hits=2.2 required=10.0 tests=HTML_MESSAGE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of javateck@gmail.com designates 209.85.220.176 as permitted sender) Received: from [209.85.220.176] (HELO mail-fx0-f176.google.com) (209.85.220.176) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 01 Apr 2009 19:41:03 +0000 Received: by fxm24 with SMTP id 24so208012fxm.29 for ; Wed, 01 Apr 2009 12:40:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:content-type; bh=VhFAm9cbkaEwiqvj3mNA+//pBkTT9d4zdmurI3zz95o=; b=tqoe8Q95MzjdUwP4cgQlP1F+COYx8RvjnjadD+GKpSUsn5soJyw1TtqT9EZVRtt7jr dNWfKD1mf4szxXEycQM8jif1f0W03eBpsTHwmJXCBnNyGLdHYV2z6VXeNHj73E2/xWEc FTCM7989FbS3zUmJbukIl+RoLiOXkZ5KgaVzA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; b=jEgek/UAZRxR3i7wMlkV8VYCo5tMKw4ITEf7tCGoOWdGoweVYH07UVSCTWR2x8C6Kd MSpwcZRt2lPWfsz7aNHeDLKB+gziKm6XKr0JRR8LZh3zDLFIdmWZjiBYaZHCVXtUs7fy Tmso7OiMp80pvPykP7wiIi3U85LEI8rYUWAUA= MIME-Version: 1.0 Received: by 10.204.70.135 with SMTP id d7mr2890562bkj.194.1238614841764; Wed, 01 Apr 2009 12:40:41 -0700 (PDT) In-Reply-To: References: <5ad58bec0904011216v11ce23bcseb6c0fc5ad0aaaed@mail.gmail.com> Date: Wed, 1 Apr 2009 12:40:41 -0700 Message-ID: <5ad58bec0904011240y15d7b2hef07fdf39d77de1@mail.gmail.com> Subject: Re: some fancy join or union support? From: javateck javateck To: hive-user@hadoop.apache.org Content-Type: multipart/alternative; boundary=001636c5a9e8a82ee90466837d36 X-Virus-Checked: Checked by ClamAV on apache.org --001636c5a9e8a82ee90466837d36 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit this is working exactly as what I want, thanks a lot, very simple, but it seems that I can't see the details from wiki site. On Wed, Apr 1, 2009 at 12:23 PM, Raghu Murthy wrote: > You can do it in one pass: > > select sum(if(col2 = 'some', 1, 0))/sum(if(col2 <> 'some', 1, 0)) from tab > > > On 4/1/09 12:16 PM, "javateck javateck" wrote: > > > can I do something like > > > > select m1.c1/m2.c2 from ((select count(col1) as c1 from tab where > > col2=='some') m1 join (select count(col1) as c2 from tab where > col2<>'some') > > m2) > > > > I know this syntax is not working, my purpose is to get the percentage > out, > > not sure if union can do it if join can't. > > --001636c5a9e8a82ee90466837d36 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable this is working exactly as what I want, thanks a lot, very simple, but it s= eems that I can't see the details from wiki site.

On Wed, Apr 1, 2009 at 12:23 PM, Raghu Murthy <rmurthy@facebook.com><= /span> wrote:
You can do it in one pass:

select sum(if(col2 =3D 'some', 1, 0))/sum(if(col2 <> 'som= e', 1, 0)) from tab


On 4/1/09 12:16 PM, "javateck javateck" <javateck@gmail.com> wrote:

> can I do something like
>
> select m1.c1/m2.c2 from ((select count(col1) as c1 from tab where
> col2=3D=3D'some') m1 join (select count(col1) as c2 from tab w= here col2<>'some')
> m2)
>
> I know this syntax is not working, my purpose is to get the percentage= out,
> not sure if union can do it if join can't.


--001636c5a9e8a82ee90466837d36--