You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Sarma Tangirala <tv...@gmail.com> on 2014/12/28 10:53:12 UTC

Row Counts From Multiple Tables

Hello,

I tried searching for this in the mailing list archive but could not find
an answer. I want to get the row counts from multiple tables, something
along the lines of

    select count(t1.first_column), count(t2.first_column) from t1, t2;

But this query as is does not work.

Apologies if this has been explained before.

Thanks
Sarma


-- 
0 1 0
0 0 1
1 1 1

Re: Row Counts From Multiple Tables

Posted by Sarma Tangirala <tv...@gmail.com>.
Oh I understand how this done, but is there a way to avoid the union all
syntax? Nothing similar to mysql?

thanks!

On 28 December 2014 at 02:12, Seungbum Lee <de...@gmail.com> wrote:

> Hello Sarma,
>
> this will work.
>
>   select sum(a.t1_cnt) as t1_cnt, sum(a.t2_cnt) as t2_cnt
>     from (select count(first_column) as t1_cnt, 0 as t2_cnt
>                from t1
>              union all
>              select 0 as t1_cnt, count(first_column) as t2_cnt
>                from t2
>            ) a
>
> good luck :)
>
> 2014-12-28 18:53 GMT+09:00 Sarma Tangirala <tv...@gmail.com>:
>
>> Hello,
>>
>> I tried searching for this in the mailing list archive but could not find
>> an answer. I want to get the row counts from multiple tables, something
>> along the lines of
>>
>>     select count(t1.first_column), count(t2.first_column) from t1, t2;
>>
>> But this query as is does not work.
>>
>> Apologies if this has been explained before.
>>
>> Thanks
>> Sarma
>>
>>
>> --
>> 0 1 0
>> 0 0 1
>> 1 1 1
>>
>
>


-- 
0 1 0
0 0 1
1 1 1

Re: Row Counts From Multiple Tables

Posted by Seungbum Lee <de...@gmail.com>.
Hello Sarma,

this will work.

  select sum(a.t1_cnt) as t1_cnt, sum(a.t2_cnt) as t2_cnt
    from (select count(first_column) as t1_cnt, 0 as t2_cnt
               from t1
             union all
             select 0 as t1_cnt, count(first_column) as t2_cnt
               from t2
           ) a

good luck :)

2014-12-28 18:53 GMT+09:00 Sarma Tangirala <tv...@gmail.com>:

> Hello,
>
> I tried searching for this in the mailing list archive but could not find
> an answer. I want to get the row counts from multiple tables, something
> along the lines of
>
>     select count(t1.first_column), count(t2.first_column) from t1, t2;
>
> But this query as is does not work.
>
> Apologies if this has been explained before.
>
> Thanks
> Sarma
>
>
> --
> 0 1 0
> 0 0 1
> 1 1 1
>