Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 3D94BCBDC for ; Tue, 29 May 2012 18:26:18 +0000 (UTC) Received: (qmail 64944 invoked by uid 500); 29 May 2012 18:26:17 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 64891 invoked by uid 500); 29 May 2012 18:26:17 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 64882 invoked by uid 99); 29 May 2012 18:26:17 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 29 May 2012 18:26:17 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of nanda.vijaydev@gmail.com designates 209.85.214.176 as permitted sender) Received: from [209.85.214.176] (HELO mail-ob0-f176.google.com) (209.85.214.176) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 29 May 2012 18:26:12 +0000 Received: by obbef5 with SMTP id ef5so10369267obb.35 for ; Tue, 29 May 2012 11:25:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=si8tUnzPqnWZoHQCVAWZpb5rZKbIbmQI18gR+OVyTQk=; b=t3YnF/P3vBgkjiVb/bePq6oZO2IvQGAi45rMHqtRwoa2EOhfj9skj2CREqxbGKq0WQ 6ahjMaAFdVYcxwolG6H3U1+aiiGOhSCPvDZ7bGt1Tf9MZBsEsjPOXhZa6V2OUCFbLpkR xJ6Tq7e14WBDh4VcFN37NYL8Hh4Ss7kGR8KFuWlvUe7nlUU+l/Ib9cBgKWE36QKDJn5q atBqf2hCCUnip42xH7rEy7UKuJoj6CJm2L3jd7ojRC5hp502cwZSuWiaYNaso3EwjsJ3 n980kRRdykjByPSt0cWo5qB4KHCeuLyZkgLrAz9UqRHxVD3tO63BBTsd091n9yfd3hpV xMeA== MIME-Version: 1.0 Received: by 10.182.192.2 with SMTP id hc2mr12091208obc.58.1338315951184; Tue, 29 May 2012 11:25:51 -0700 (PDT) Received: by 10.182.81.195 with HTTP; Tue, 29 May 2012 11:25:50 -0700 (PDT) In-Reply-To: References: Date: Tue, 29 May 2012 11:25:50 -0700 Message-ID: Subject: Re: Need help with simple subquery From: Nanda Vijaydev To: user@hive.apache.org Content-Type: multipart/alternative; boundary=14dae9399df7de1a1304c130f61f X-Virus-Checked: Checked by ClamAV on apache.org --14dae9399df7de1a1304c130f61f Content-Type: text/plain; charset=ISO-8859-1 Use a join as given below, I have used the table name temp. select a.Category, a.count, b.tot_count, (100*a.count)/tot_count as percent_count from ( select Category, count(*) as count from temp group by Category ) a join (select count(*) as tot_count from temp) b on (true) Hope this helps Nanda Vijaydev On Mon, May 28, 2012 at 3:28 PM, Igor Tatarinov wrote: > Try replacing the comma with JOIN > > igor > decide.com > > On Mon, May 28, 2012 at 6:48 AM, shan s wrote: > >> I need help with a simple subquery. Given below data, I need counts and >> percentage counts per category. (Re-phrasing my earlier question ) >> With the code below I get an error: *FAILED: Parse Error:* line 6:50 *mismatched >> input ','* *expecting EOF near 'a'* >> >> Looking at the documentation the syntax it is not clear to me.. In the >> form clause, can I have multiple selects and just alias them and use them >> in the top query? >> If so, what am I missing in the below code... >> >> select a.category, a.count, b.totalCount >> from >> (select category, count(*) as count from gt group by category) a, >> (select count(*) as totalCount from gt) b ; >> >> Many Thanks, >> Prashant. >> >> Id Category Count Output Percentage Output >> 1 A A 4 A 40 (4/10)*100 >> 2 A B 3 B 30 >> 3 B C 3 C 30 >> 4 A >> 5 C >> 6 C >> 7 B >> 8 B >> 9 A >> 10 C >> > > --14dae9399df7de1a1304c130f61f Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Use a join as given below, I have used the table name temp.

select a= .Category, a.count, b.tot_count, (100*a.count)/tot_count as percent_count <= br>from=A0=A0 ( select Category, count(*) as count from temp group by Categ= ory ) a
=A0=A0=A0 join (select count(*) as tot_count from temp) b
=A0=A0=A0=A0 = on (true)

Hope this helps
Nanda Vijaydev

On Mon, May 28, 2012 at 3:28 PM, Igor Tatarinov <igor@decide.com> wrote:
Try replac= ing the comma with JOIN

igor
decide.= com

On Mon, May 28= , 2012 at 6:48 AM, shan s <mysub987@gmail.com> wrote:
I need help with a simple subquery. Given below data, I need counts an= d percentage counts per category. (Re-phrasing my earlier question )
With the code below I get an error: FAILED: Parse Error: line 6= :50 mismatched input ',' expecting EOF near 'a'
=A0
Looking=A0at the documentation the syntax it is not clear to me..=A0In= the form clause, can I have multiple selects and just alias them and use t= hem in the top query?
If so, what am I missing in the below code...
=A0
select a.category, a.count, b.totalCount
from
(select category, = count(*) as count from gt group by category) a,
(select count(*) as tota= lCount from gt) b ;
=A0
Many Thanks,
Prashant.

Id Category=A0=A0=A0=A0=A0=A0=A0=A0 Count Output=A0=A0 Percentage = Output
1=A0=A0=A0 A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0 A=A0=A0=A0=A0=A0 4=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 A=A0=A0=A0=A0= 40 (4/10)*100
2=A0=A0=A0 A=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0 B=A0=A0=A0=A0=A0 3=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 B=A0=A0= =A0=A0 30
3=A0=A0=A0 B=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 C=A0=A0=A0=A0=A0 3=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 C=A0=A0=A0= =A0 30
4=A0=A0=A0 A
5=A0=A0=A0 C
6=A0=A0=A0 C
7=A0=A0=A0 B
8=A0=A0=A0 = B
9=A0=A0=A0 A
10=A0 C


--14dae9399df7de1a1304c130f61f--