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:47:33 GMT
Thanks, Ali. But this is actually a variant of the solution that I 
proposed. The main problem that I see with it is that you need to 
duplicate the WHERE field of the main query in all the subqueries and 
this is just an overkill.

Regards,
Robert

Suavi Ali Demir wrote:
> 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