CODEWITHSUNDEEP
hivehiveql
Vikrant Goel
Vikrant Goel

Reputation: 654

Division over different select count queries in Hive

I have two tables and want to find the percentage of different records between them.

TableA(item int,value string)
TableB(item int,value string)

I wrote this query that works perfectly with MYSQL

SELECT 100*
       (SELECT COUNT(*) 
          FROM TableA A, 
               TableB B
         WHERE A.item=B.item
               AND A.value!=B.value
       )
       /
       (SELECT COUNT(*) 
          FROM TableA A
       )
 ;

But for a similar scenario in HIVE, this query doesn't work and fails with NoViableAltException How can I do these divisions of counts in HIVE?

Upvotes: 1

Views: 2276

Answers (1)

Beryllium
Beryllium

Reputation: 12988

You can get the values in two sub queries, and then calculate in the outer query:

select
    100 * different / total
  from
    (select
         count(*) as different
       from
         a, b
       where
         a.id = b.id and a.value <> b.value) t1,
    (select
         count(*) as total
       from
         a) t2

Upvotes: 1

Related Questions