Re: aggregate function ? - Mailing list pgsql-novice

On 16/05/2007, at 18:39, Richard Broersma Jr wrote:

>
> --- Raimon Fernandez <coder@montx.com> wrote:
>
>> Well, that's perfectly ...
>>
>> Now I'm going to try to understand how this work ...
>>
>> In the web page that you refer, it also shows how to do it with a
>> temporal table/cursor, and I see it's faster there.
>>
>> You have more options if you need to do some extra operations, but I
>> see how powerful SQL can be ...
>
> Really, from a SQL pure-ist point of view, all operations on the
> database can be and SHOULD BE
> preformed with SQL statements as the initial development effort.
> The only reason to change a
> procedural solution (such as using a cursor) would be if you can
> achieve better performance by
> using a procedure.  However, I would not take the performance
> results from that web site
> seriously.  The are most likely not using PostgreSQL in there
> test.  And PostgreSQL may favor one
> of the query methods over the procedure.  You will have to test for
> your self which solution is
> the best for your environment.

I'll try to do it as an exercise, and because I'm a programmer, I can
see the solution easier from my point of view, but that doesn't mean
that my solution will be better ...


>> Would be possible with this code to start the sum_value with a
>> previous value from another SELECT ?
>>
>> For example, SELECT SUM(deure)-SUM(haver) FROM assentaments WHERE
>> data<2007-01-01;
>
> Sure, you can achieve what you are looking for.  However, you have
> to know exactly what you want
> to achieve AND you have to know the predicates to use to achieve
> these results.  As to can tell,
> for the previous SQL link, there can be various solutions to a
> problem.  So have performance
> advantages over others.

I'm going to use this way:

the user enter two dates, and I perform the query with the date field
instead of the numero field.

so I'would have to query first from the very first row of data till
the first data entered by the user, and store that value as the
initial sum_value, and later apply the code you sent me, that works
great ...



> Some of the values that you could tinker with that would can the
> results
> of the query would be:
>
> ...
>        AND A1.numero = A2.numero
>      WHERE A1.numero=11189
> ...

I don't understand this ...


as a conclusion, I'll need a really good book and plenty of time to
play and work with SQL ...

regards and many thanks !

raimon