db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Segel" <mse...@segel.com>
Subject RE: How to emulate multiple DISTINCT aggregates?
Date Tue, 29 Aug 2006 13:25:51 GMT
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:robert.enyedi@intland.com]
> 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
> 
> derby@segel.com 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:demir4@yahoo.com]
> > *Sent:* Monday, August 28, 2006 3:52 PM
> > *To:* Derby Discussion; msegel@segel.com
> > *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
> >
> >
> > */derby@segel.com/* 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:robert.enyedi@intland.com]
> >     > 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
> >




Mime
View raw message