CODEWITHSUNDEEP
sqlhivehiveql
tash
tash

Reputation: 11

How do I add two counts in HIVE hql?

So I am aware I can get the count of one table by using select count(*) from table1;

I have tried select(select count() from table1) table1, (select count() from table2) table2 from dual;

However it does not work.

Upvotes: 1

Views: 255

Answers (1)

leftjoin
leftjoin

Reputation: 38290

Two possible solutions. Cross join and Union all + aggregation

Cross join:

 select t1.cnt as table1_count, 
        t2.cnt as table2_count
 from
      (select count(*) cnt from table1) t1
       cross join
      (select count(*) cnt from table2) t2

Union all + max aggregation:

select max(t1_cnt) table1_count, max(t2_cnt) table2_count
from
(
select count(*) t1_cnt, 0 t2_cnt from table1
union all
select 0 t1_cnty, count(*) t2_cnt from table2
) s

Upvotes: 1

Related Questions