James Thanks very much for your help. This worked for me:- > select count(distinct col1 || col2) ... >
Regards Mike From: James Taylor [mailto:[email protected]] Sent: Tuesday, June 23, 2015 9:00 PM To: user Subject: Re: count distinct Michael, You're correct, count distinct doesn't support multiple arguments currently (I filed PHOENIX-2062 for this). Another workaround is to combine a.col1 and b.col2 into an expression, for example concatenating them. If order matters, you could do this: select count(distinct col1 || col2) ... If order doesn't matter, you could do this: select count(distinct case when col1 > col2 then col1 || col2 else col2 || col1 end) ... Thanks, James On Tue, Jun 23, 2015 at 6:56 PM, Michael McAllister <[email protected]<mailto:[email protected]>> wrote: > 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<tel: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
