Return-Path: 
 <user-return-8651-apmail-hive-user-archive=hive.apache.org@hive.apache.org>
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 1224890CB
	for <apmail-hive-user-archive@www.apache.org>;
 Tue, 22 May 2012 05:40:43 +0000 (UTC)
Received: (qmail 81439 invoked by uid 500); 22 May 2012 05:40:42 -0000
Delivered-To: apmail-hive-user-archive@hive.apache.org
Received: (qmail 80670 invoked by uid 500); 22 May 2012 05:40:36 -0000
Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-help@hive.apache.org>
List-Unsubscribe: <mailto:user-unsubscribe@hive.apache.org>
List-Post: <mailto:user@hive.apache.org>
List-Id: <user.hive.apache.org>
Reply-To: user@hive.apache.org
Delivered-To: mailing list user@hive.apache.org
Received: (qmail 80624 invoked by uid 99); 22 May 2012 05:40:34 -0000
Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230)
    by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 22 May 2012 05:40:34 +0000
X-ASF-Spam-Status: No, hits=1.7 required=5.0
	tests=FREEMAIL_ENVFROM_END_DIGIT,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (nike.apache.org: domain of nitinpawar432@gmail.com
 designates 209.85.215.48 as permitted sender)
Received: from [209.85.215.48] (HELO mail-lpp01m010-f48.google.com)
 (209.85.215.48)
    by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 22 May 2012 05:40:27 +0000
Received: by lagz14 with SMTP id z14so5661802lag.35
        for <user@hive.apache.org>; Mon, 21 May 2012 22:40:06 -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=6L5FJv7TAPZfPx+dhoZTONMhFviaUXfVHzcnlRq7eUw=;
        b=ZXB57R6mTjbg/Ez2pDEEQVpuGsPvKhp62K2LCTwNp7ueHv/y661/Sx8WBU2QTsmTgn
         lOBBvPnsnFL41AfZFLubiVPqqTF8cp6uOG2B6duvYctOn0rKvLjBMK6ElcuKhjVM8pkV
         SlRZnycJSDyHIjxz0cdRW85EReK7xKOwCHt+urCJoxbmZZe9pHUs7ic1RTZAmtaAZXmJ
         Ii4oHEfVOi3l6sWCxbmwZvC1o0+w6b3kaUeQJQzDjVWB/Chzv8WPCqVqwKJEc5nJRj2e
         lWRXg4l9l7I35zr8tHT5gCERXefrrW8hWvLcX0uBNiCiLf8HSOYa5KxlZX9P/S3UwKYz
         Y/Wg==
MIME-Version: 1.0
Received: by 10.112.102.136 with SMTP id fo8mr9787945lbb.106.1337665206256;
 Mon, 21 May 2012 22:40:06 -0700 (PDT)
Received: by 10.112.113.35 with HTTP; Mon, 21 May 2012 22:40:06 -0700 (PDT)
In-Reply-To: 
 <CAEcKwteiN7sYwoF9Cn3uEEx64cpijwm-G2Qw9CYC5VvgZgNB9w@mail.gmail.com>
References: 
 <CAEcKwteiN7sYwoF9Cn3uEEx64cpijwm-G2Qw9CYC5VvgZgNB9w@mail.gmail.com>
Date: Tue, 22 May 2012 11:10:06 +0530
Message-ID: 
 <CAORpBsgs0qqJiKoOFT6S_3mn+oY4_a6+Mf4Jrrp0jinSF+jhgg@mail.gmail.com>
Subject: Re: Multi Table Join & count Distinct
From: Nitin Pawar <nitinpawar432@gmail.com>
To: user@hive.apache.org
Content-Type: multipart/alternative; boundary=f46d0401fa337282e404c09973cd

--f46d0401fa337282e404c09973cd
Content-Type: text/plain; charset=ISO-8859-1

try removing C1,C2 and C3 from select

count should give you exactly one output, where as c1,c2,c3 may vary as per
table

On Tue, May 22, 2012 at 5:02 AM, cmdr taco <freshair1947@gmail.com> wrote:

> When I try to run below query, but for some reason I'm
> getting weird results  for the  C2_CNT, its not giving distinct value count
>
>
> SELECT
>  C1,C2,C3
> ,COUNT(DISTINCT B.C2) C2_CNT
> ,COUNT(C.C2) CC_CNT
> FROM
> TABLE_A A
> JOIN TABLE_B B on (A.C1=B.C1  )
> left outer join  TABLE_C C on (B.C2 = C.C2  )
> group by C1,C2,C3
>
>
>
>


-- 
Nitin Pawar

--f46d0401fa337282e404c09973cd
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

try removing C1,C2 and C3 from select=A0<div><br></div><div>count should gi=
ve you exactly one output, where as c1,c2,c3 may vary as per table=A0<br><b=
r><div class=3D"gmail_quote">On Tue, May 22, 2012 at 5:02 AM, cmdr taco <sp=
an dir=3D"ltr">&lt;<a href=3D"mailto:freshair1947@gmail.com" target=3D"_bla=
nk">freshair1947@gmail.com</a>&gt;</span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">When I try to run below query, but for some =
reason I&#39;m getting=A0weird=A0results =A0for the=A0
C2_CNT, its not giving distinct value count<div><br><div><br></div><div><di=
v>SELECT</div><div>=A0C1,C2,C3</div><div>,COUNT(DISTINCT B.C2) C2_CNT</div>=
<div>,COUNT(C.C2) CC_CNT</div><div>FROM</div><div>TABLE_A A</div><div>JOIN =
TABLE_B B on (A.C1=3DB.C1 =A0)</div>

<div>left outer join =A0TABLE_C C on (B.C2 =3D C.C2 =A0)</div><div>group by=
 C1,C2,C3</div></div></div><div><br></div><div><br></div><div><br></div>
</blockquote></div><br><br clear=3D"all"><div><br></div>-- <br>Nitin Pawar<=
br><br>
</div>

--f46d0401fa337282e404c09973cd--