> Your second query should work with Phoenix 4.3 or later. > Thanks, unfortunately at the moment I’m stuck with Phoenix 4.2.
> I will investigate the problem with the first one and get back to you. > Appreciate this. Michael McAllister Staff Data Warehouse Engineer | Decision Systems [email protected]<mailto:[email protected]> | C: 512.423.7447 | skype: michael.mcallister.ha<mailto:[email protected]> | webex: https://h.a/mikewebex [cid:[email protected]] This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment. On Jun 23, 2015, at 7:54 PM, Maryann Xue <[email protected]<mailto:[email protected]>> wrote: Sorry, I missed the first line. Your second query should work with Phoenix 4.3 or later. I will investigate the problem with the first one and get back to you. Thanks, Maryann On Tuesday, June 23, 2015, Michael McAllister <[email protected]<mailto:[email protected]>> wrote: 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
