db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Enyedi <robert.eny...@intland.com>
Subject Re: How to emulate multiple DISTINCT aggregates?
Date Tue, 29 Aug 2006 07:43:01 GMT
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