SemanticException [Error 10128]: Not yet supported place for UDAF 'sum' in Hive/Hue

Question

From this sentence:

SELECT a.comunity, sum(b.cont_woman),sum(b.cont_men)

FROM cont_per_comunity.states_per_comunities a

JOIN cont_per_comunity.cont_per_state b

ON a.state = b.state

WHERE sum(b.cont_woman) >= sum(b.cont_men)

GROUP BY a.comunity;

I get the following error:

Error occurred executing hive query: Error while compiling statement: FAILED: SemanticException [Error 10128]: Line 9:6 Not yet supported place for UDAF 'sum'

Is there another way to select the sum of the data?

Answer 1

You need to do that in a having clause, or in an outer query. You can't use aggregate functions in a where clause like you are trying to do.

Try this:

SELECT a.comunity, sum(b.cont_woman),sum(b.cont_men)
FROM cont_per_comunity.states_per_comunities a
JOIN cont_per_comunity.cont_per_state b
ON a.state = b.state
GROUP BY a.comunity
having sum(b.cont_woman) >= sum(b.cont_men)

Or

select * from (
    SELECT a.comunity, sum(b.cont_woman) as cont_woman
    ,sum(b.cont_men) as cont_men
    FROM cont_per_comunity.states_per_comunities a
    JOIN cont_per_comunity.cont_per_state b
    ON a.state = b.state
    GROUP BY a.comunity ) t
    where cont_woman >= cont_men