You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@hive.apache.org by cmdr taco <fr...@gmail.com> on 2012/05/22 01:32:12 UTC

Multi Table Join & count Distinct

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

Re: Multi Table Join & count Distinct

Posted by Nitin Pawar <ni...@gmail.com>.

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 <fr...@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