Re: aggregate function ?

Begin forwarded message:

> From: Raimon Fernandez <coder(at)montx(dot)com>
> Date: 16 maig 2007 01:04:10 GMT+02:00
> To: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
> Subject: Re: [NOVICE] aggregate function ?
>
> I see I wasn't clear enough ...
>
> All the records are from the same table, the letters were just row
> data.
>
> So I should know the value of the row that is before the one I'm
> going to fetch ...
>
>
> thanks!
>
> raimon
>
>
> On 15/05/2007, at 23:59, Richard Broersma Jr wrote:
>
>>
>> --- Raimon Fernandez <coder(at)montx(dot)com> wrote:
>>
>>> hi again,
>>>
>>>
>>> Some days ago I asked for help, nobody replied, and after trying to
>>> do it in some way, I think aggregate function is the solution to my
>>> problem, but I found difficult to understand how it works ...
>>>
>>> What I want to do is accumulate the value for each row and add it to
>>> the next:
>>>
>>> a 100 100
>>> b 50 150
>>> c 25 175
>>>
>> One solution is to use a correlated sub-query:
>>
>> SELECT A.letter_field, A.letter_value,
>> ( SELECT sum( letter_value )
>> FROM Your_table AS B
>> WHERE B.letter_field <= A.letter_field ) AS value_sum
>> FROM Your_table AS A
>> ORDER BY A.letter_field;
>>
>> Another solution is to use a join:
>>
>> SELECT A.letter_field, A.letter_value,
>> sum( B.letter_value ) AS value_sum
>> FROM Your_table AS A
>> INNER JOIN Your_table AS B
>> ON A.letter_field <= B.letter_field
>> GROUP BY A.letter_field, A.letter_value,
>> ORDER BY A.letter_field;
>>
>> There are a couple of good SQL books that are really good at
>> teaching methods on how to construct
>> such queries if you are enterested.
>>
>> Regards,
>> Richard Broersma Jr.
>>
>>
>