Hi
(This questions relates to Phoenix 4.2 on HDP 2.2)
I have a situation where I want to count the distinct combination of a couple
of columns.
When I try the following:-
select count(distinct a.col1, b.col2)
from table tab1 a
inner join tab2 b on b.joincol = a.joincol
where a.col3 = ‘some condition’
and b.col4 = ‘some other condition';
I get the following error:-
Error: ERROR 605 (42P00): Syntax error. Unknown function: "DISTINCT_COUNT".
(state=42P00,code=605)
Playing around with this it looks like count(distinct) works with a single
column, but not more.
So I try this:-
SELECT count(*)
FROM
(SELECT a.col1,
b.col2
FROM TABLE tab1 a
INNER JOIN tab2 b ON b.joincol = a.joincol
WHERE a.col3 = ‘SOME condition’
AND b.col4 = ‘SOME other condition'
GROUP BY a.col1,
b.col2) ;
I get the following error:-
Error: Complex nested queries not supported. (state=,code=0)
So, my question … is there any way to get what I’m looking for?
Regards,
Mike
