Ok, Yeah, you're right. I'm trying to think about what I did in my query that gave me the right result.
In my test table, there were 65K rows and I was able to get the correct count. So I guess I'll have to go back to see what I did. > -----Original Message----- > From: Robert Enyedi [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 29, 2006 2:43 AM > To: Derby Discussion > Subject: Re: How to emulate multiple DISTINCT aggregates? > > I can confirm what Ali said because {3,3,3} is the result I also receive > on Derby. > > However, it seems the correct result to me. Just think about it: > > SELECT COUNT(tmp1.f1) > FROM tmp1 > WHERE tmp1.f1 in ( SELECT DISTINCT b.f1 FROM tmp1 b) > > This basically counts the number of values field f1 has. > > Regards, > Robert > > [EMAIL PROTECTED] wrote: > > > > That doesn't make sense. > > > > Each sub select statement should have returned the following > > > > Field A {1,2} > > > > Field B {1,2} > > > > Field C {1,2,3} > > > > Then in the original select, your counts should be {2,2,3}; > > > > Note: I used Informix. > > > > There is something interesting going on. You're using a dummy table as > > a wrapper for 3. > > > > Now I wonder what the optimizer thinks of this and how, if at all, > > does it parallelize the query? > > > > I'll try my sql method on a similar table to yours, but I do think the > > response you saw wasn't correct. > > > > ------------------------------------------------------------------------ > > > > *From:* Suavi Ali Demir [mailto:[EMAIL PROTECTED] > > *Sent:* Monday, August 28, 2006 3:52 PM > > *To:* Derby Discussion; [EMAIL PROTECTED] > > *Subject:* RE: How to emulate multiple DISTINCT aggregates? > > > > That query needs to be modified little bit. It does not work when > > > > my table contains: > > > > 1 > > > > > > > > 1 > > > > > > > > 1 > > > > 2 > > > > > > > > 2 > > > > > > > > 2 > > > > 1 > > > > > > > > 1 > > > > > > > > 3 > > > > result is 3,3,3, where as it should have been 2,2,3. > > > > This one works: > > > > SELECT > > (SELECT COUNT(DISTINCT b.field1) FROM table_a b) as field1_count, > > (SELECT COUNT(DISTINCT c.field2) FROM table_a c) as field2_count, > > (SELECT COUNT(DISTINCT d.field3) FROM table_a d) as field3_count > > FROM SYSIBM.SYSDUMMY1 > > > > Regards, > > > > Ali > > > > > > */[EMAIL PROTECTED]/* wrote: > > > > > > Hi, > > > > Uhm you can't do what you want to do as written.... > > > > When you say SELECT COUNT(DISTINCT blah), it has to be unique to > > your query. > > So no multiple COUNT(DISTINCTS xxx) allowed in a single query. > > However here's a work around that may help... > > > > SELECT COUNT(a.field1), COUNT(a.field2), COUNT(a.field3) ... > > FROM table_a a > > WHERE a.field1 in ( SELECT DISTINCT b.field1 FROM table_a b) > > AND a.field2 in ( SELECT DISTINCT b.field2 FROM table_a b) > > AND a.field3 in ( SELECT DISTINCT b.field3 FROM table_a b) > > ... > > > > Well you get the idea. > > > > It's then pretty straight forward. > > Each subselect returns the distinct set of elements and then your > > outer > > select returns the count on these unique subsets. > > > > I just tried it in Informix (I happen to have a table with a couple > of > > string columns of data that I am working on.) > > Should work in Derby. Its all standard SQL > > > > But Hey! What do I know? > > > > -G > > > > > -----Original Message----- > > > From: Robert Enyedi [mailto:[EMAIL PROTECTED] > > > Sent: Monday, August 28, 2006 10:58 AM > > > To: Derby Discussion > > > Subject: How to emulate multiple DISTINCT aggregates? > > > > > > Hi, > > > > > > When trying to use multiple DISTINCT aggregates in Derby: > > > > > > /SELECT COUNT(distinct editable), COUNT( distinct visible ) > > > FROM item/ > > > > > > the following error is reported: > > > > > > /ERROR 42Z02: Multiple DISTINCT aggregates are not supported at > this > > > time./ > > > > > > For a simple query one could write: > > > > > > /SELECT COUNT(distinct editable) AS editable_count, COUNT( (SELECT > > > DISTINCT visible FROM item) ) > > > FROM item/ > > > > > > but this is actually pretty uncomfortable and unmaintainable in a > > larger > > > query. > > > > > > Does anyone know an elegant overcome for this Derby limitation? > > > > > > Thanks, > > > Robert > >
