Derby Aggregate Function

[bushmonger <mi...@wingateweb.com>]

I wish to create an aggregate function to comma delimit grouped-by results. 
I need an example of a Sql Routine for SUM() or COUNT()... so I can create
this "COMMADELIM" function.  MySQL, Oracle, SQLServer all provide ways to
build this or come with this default.

Here is an example data scenario:

create table orders (
  order_id   integer,
  product    varchar(100)
);

insert into orders (order_id, product) values(1, 'Shoe');
insert into orders (order_id, product) values(1, 'Boot');
insert into orders (order_id, product) values(1, 'Sandal');

select order_id, commadelim(product) products from orders group by order_id;

This would produce the results:

order_id          products
-------------------------------------------------
1                    Shoe, Boot, Sandal

Anyone have examples or ideas?
-- 
View this message in context: http://www.nabble.com/Derby-Aggregate-Function-tf4735783.html#a13542880
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Derby Aggregate Function

[Rick Hillegas <Ri...@Sun.COM>]

Cloudscape (the codeline which Derby is based on) used to include 
user-defined aggregates, which you could declare with a CREATE AGGREGATE 
statement. DERBY-672 tracks the effort to put user-defined aggregates 
into Derby. It sounds as though you might want to volunteer for this 
effort. That would be great! I would start by searching the codeline for 
"AggregateDefinition".

Hope this helps,
-Rick

bushmonger wrote:
> I wish to create an aggregate function to comma delimit grouped-by results. 
> I need an example of a Sql Routine for SUM() or COUNT()... so I can create
> this "COMMADELIM" function.  MySQL, Oracle, SQLServer all provide ways to
> build this or come with this default.
>
> Here is an example data scenario:
>
> create table orders (
>   order_id   integer,
>   product    varchar(100)
> );
>
> insert into orders (order_id, product) values(1, 'Shoe');
> insert into orders (order_id, product) values(1, 'Boot');
> insert into orders (order_id, product) values(1, 'Sandal');
>
> select order_id, commadelim(product) products from orders group by order_id;
>
> This would produce the results:
>
> order_id          products
> -------------------------------------------------
> 1                    Shoe, Boot, Sandal
>
> Anyone have examples or ideas?
>