Subject Re: [firebird-support] PSQL: Getting both aggregation and individual rows
Author Svein Erling Tysvær
> I tried this approach before, but it violates my "only run GET_DATA
>once" requirement :-(

Well, with EXECUTE BLOCK and ORDER BY it may be possible to satisfy your MIN requirement, but I can't logically understand how it even theoretically wold be possible to know the SUM before having gone through all records. Window functions in Fb 3 is probably the best answer to your question, but I'd still guess Fb would do two passes under the hood.

Not that I think it matters too much, I assume the first pass to be more costly than the second. Have you measured how much more time is required to return the entire result set if having the aggregate functions compared to a simpler query without them (but with ORDER BY since that makes it more easily comparable)?


On 21.10.2014 21:15, Svein Erling Tysvær
svein.erling.tysvaer@... [firebird-support] wrote:
> What about
> FOR WITH TMP(MyDateTime, MySum) as
> (SELECT MIN(dateandtime), SUM(Value)
> FROM gen_data(...))
> SELECT,, g.dateandtime, g.value, t.MyDateTime, tMySum
> FROM gen_data(...) g
> CROSS JOIN tmp t
> Of course, you may want a different join to CROSS JOIN.
> HTH,
> Set