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
> -----Original Message-----
> From: [email protected] [mailto:firebird-
> [email protected]]
> Sent: Tuesday, October 21, 2014 4:12 AM
> To: [email protected]
> 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
>
>
>
