Search code examples
hadoophive

How Hive ql do query with multiple COUNT function and make a divides methon with them

Here my question: I have a table with some records like (name, date, type). Suppose I have three type a, b and c.Now I want to count each type as type_count with some restriction and make a division with count(a)/count(b) to make a percent result, and the restriction in a and are different, how can I deal with this? thanks! my code look like this:

SELECT name, count(a), count(a)/count(b)
from table
where ...

Is it possible to make some subquery in select? look like this

select name, count(a), count(a)/ (select count(b) from table where restriction_for_b)
from table
where retriction_for_a
Solution

  • If I understand your question correctly, you can replace the counts with sum(if(condition, 1, 0)). Something like this:

    select
  name,
  sum(if(condition_for_a, 1, 0)),
  sum(if(condition_for_a, 1, 0)) / sum(if(condition_for_b, 1, 0))
from table
where ...