Re: aggregate function ?

[Richard Broersma Jr <rabroersma(at)yahoo(dot)com>]

--- Raimon Fernandez <coder(at)montx(dot)com> wrote:
> This returns 3217 rows, and the value_sum is ok, but it takes too  
> long (89.45 sec)
> 
> Can anyone confirm that it's doing for every row the  
> 'starting_sum' (first select), and if so, how to do it just once ?

Yes, any sub-select in the Select expression list will be executed many times. To fix this, you
will need to reform your query by pushing this sub-select down to the from clause.  Also you will
need to add clau_compte to the SELECT expression list of both sub-Selects

    SELECT <your expression list>, Summed_assentaments.delta_sum + Initialvalue.starting_sum
      FROM ( <your first initial sub-select> ) AS Summed_Assentaments( oid, concepte,deure, haver,
delta_sum, clau_compte )
INNER JOIN ( <your initial value sub-select> ) AS Initialvalue( starting_sum, clau_compte )
        ON Summed_Assentaments.clau_compte = Initialvalue.clau_compte;

I hope this can help to improve query time.

Regards,
Richard Broersma Jr.