Re: aggregate function ?

Hello again,

Is this the correct way to add a number to each sum_value ?

SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
(SELECT deure FROM assentaments WHERE oid=180108)+ sum
( COALESCE( A2.deure, 0 ) -
COALESCE( A2.haver, 0 )) AS value_sum
FROM Assentaments AS A1
INNER JOIN Assentaments AS A2
ON A1.oid >= A2.oid
AND A1.numero = A2.numero
WHERE A1.numero=11189
GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
ORDER BY A1.oid;

I think it's not the correct way, as I believe it SELECTS the value
for each row, so it's getting longer ...

I'm trying also with a SELECT INTO target select_expressions
FROM ...; but not found the right way yet ...

thanks again,

raimon

On 16/05/2007, at 18:54, Raimon Fernandez wrote:

>
> On 16/05/2007, at 18:39, Richard Broersma Jr wrote:
>
>>
>> --- Raimon Fernandez <coder(at)montx(dot)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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>