[SOLVED] math operations between queries Impala SQL

math operations between queries Impala SQL

i need to divide the results coming from two different queries in Impala through the HUE editor.

The query i wrote in Oracle is shown below:

select
 (select count(distinct t1.ids)
  from table1 t1
  where extract(year from t1.insertdate)=2020)
  /
 (select count(distinct t2.ids)
  from table2 t2
  where extract(year from t2.insertdate)=2019)
from dual

On impala the same query does not work due to "/" operator. Can you please explain me how to do the same thing in Impala SQL?

Solution

You can join them on a dummy column and then divide the result sets.


SELECT cnt1.cnt1/cnt2.cnt2
FROM
  (SELECT count(DISTINCT t1.ids) cnt1, 'dummy' dum
   FROM table1 t1
   WHERE YEAR (t1.insertdate)=2020) cnt1
JOIN
  (SELECT count(DISTINCT t2.ids) cnt2, 'dummy' dum
   FROM table2 t2
   WHERE YEAR (t2.insertdate)=2019) cnt2 
ON cnt1.dum= cnt2.dum -- dummy column