Subject Re: [firebird-support] Aggregation by concatenation
Author Jonathan Neve

(I had, by mistake, made my initial post to FireBird-Support instead of
FireBird-Architect. I'm therefore sending a copy of this reply to
FireBird-Architect, and redirecting followups there.)

Arno Brinkman wrote:

>>Hi all,
>>I had an idea of a new feature which I think could be useful, please
>>tell me what you think. Currently, it's possible to aggregate a column
>>as a sum, an average, an minimum or a maximum. It would be useful, in
>>certain cirsumstances, to be able to aggregate a column by concatenating
>>all the values, or perhaps by putting them all into an array.
>>Jonathan Neve.
> <>There are many problems with this.
> 1) What is the output datatype for the SUM (this needs to be known at
> prepare time)?

How about an array? This would also make the individual elements easier
to access, so that they can then be manipulated in any way necessary,
not necessarily by concatenation.

>2) I do this on 1.000 records with a average of 500 characters per record?
You could. It would probably still be faster than fetching 1000 records
or 500 characters each, and doing it all on the client side.
But I think this would mostly be useful in cases where there are a small
number of records being grouped together, because it would in such cases
be a simple alternative to having a separate query for getting this

Consider the following example: Suppose I have a list of invoices. I
want to sum up all the relevant information, grouping by customer for
example. It could also be useful to be able to have the list of all the
invoice numbers of the corresponding invoices. This is bound (or so it
seems to me) to be faster (and simpler) than executing a separate query.
It seems to me that it shouldn't be much slower to add an element to an
array for each record that gets aggregated than to sum up the values...

>I would say, write a SP :-)
Sure, I could do this if I really had to, but I'd probably find an
alternative solution before going that far. I'd say that using an
aggregation operator is much simpler (to use).

Jonathan Neve.

[Non-text portions of this message have been removed]