On 21.10.2014 20:48, 'Edward Mendez' Emendez001@...
[firebird-support] wrote:

>
>
> Josef,
>
> Not sure if this will be efficient depending on number of rows, but this is
> another way without using a TEMP_TABLE.
>
> SELECT gen_data.id, gen_data.name, gen_data.dateandtime, gen_data.value,
> T1.min_dateandtime, T1.total_value
> FROM gen_data(...)
> INNER JOIN (SELECT name, min(dateandtime) min_dateandtime,
> sum(value) total_value from Gen_Data(...) group by name ) T1 on (T1.name =
> gen_data.name)
> INTO...
> DO...
>
> Thanks,
>
> Edward Mendez

Hi!

Thanks for the suggestion. Unfortunately, this runs GEN_DATA twice, and
that it a problem. I don't mind a performance penalty, but I can't run
the data generating procedure twice.

Josef

>
>> -----Original Message-----
>> From: firebird-support@yahoogroups.com [mailto:firebird-
>> support@yahoogroups.com]
>> Sent: Tuesday, October 21, 2014 4:12 AM
>> To: firebird-support@yahoogroups.com
>> Subject: Re: [firebird-support] PSQL: Getting both aggregation and
> individual
>> rows
>>
>> Seems like I will have to use a temporary table for this purpose. But if
> there
>> is another way, I would be interested in knowing it.
>>
>> Josef
>>
>> > Hi!
>> >
>> > Is there a simple way to get an aggregation of some query as well as
>> > the individual rows from within a PSQL? I.e., I have a stored
>> > procedure GEN_DATA which produces individual rows (ID, Name,
>> DateAndTime, Value).
>> > I am processing the output of GEN_DATA in another stored procedure
>> > PROCESS_DATA:
>> >
>> > FOR SELECT id, name, dateandtime, value
>> > FROM gen_data(...)
>> > INTO ...
>> > DO ...
>> >
>> > Within this loop, I need the individual records, but I also need some
>> > aggregations on them, i.e. MIN(dateandtime) or SUM(value). If I could
>> > run GEN_DATA twice, then it would be easy (in the first run I would
>> > calculate the aggregations, in the second run the individual values),
>> > but I can't. If I could perform the processing in my application
>> > (rather than the stored procedure), it would be easy, too (I would
>> > simply traverse the result dataset of GEN_DATA twice), but again I
>> > can't do that. Would Common Table Expressions or perhaps Derived
>> Tables help me?
>> >
>> > Thanks,
>> >
>> > Josef
>>
>>
>>
>> ------------------------------------
>>
>> ------------------------------------
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>> ++
>>
>> Visit http://www.firebirdsql.org and click the Documentation item on the
>> main (top) menu. Try FAQ and other links from the left-side menu there.
>>
>> Also search the knowledgebases at
>> http://www.ibphoenix.com/resources/documents/
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>> ++
>> ------------------------------------
>>
>> Yahoo Groups Links
>>
>>
>>
>
>