Re: aggregate function ?

Hi Richard,

Ok, now I understand ...

Any recomended good book for SQL ?

On 16/05/2007, at 01:57, Richard Broersma Jr wrote:

>> All the records are from the same table, ...
>
> True. But notice that the examples that I provided also referenced
> only one table. If you look
> closely, you will see that I am querying the table twice. Once
> ( with the alias name "A" ) to find
> your list of rows and once ( with the alias name "B" ) to find the
> running total.

ok,

>> ... the letters were just row
>> data.
>
> Yes, I expected that you were simplifying you data in the example
> that you used. However, since
> you were able to abstract you row data as single letters,
> conversely you should be able to extend
> the example I provided to fit your table's design.
>
>> So I should know the value of the row that is before the one I'm
>> going to fetch ...

ok

> I do not understand you question here.

Yes, that for getting the accumulate of line 2 (50) first I have to
know the accumulate of line 1 (75)

Maybe with this example is more clear ...

Table assenatments
column 1: oid
column 2: detail
column 3: value_d
column 4: value_h
column 5: (accumulate value_d)-(accumulate(value_h)

1 invoice 75 0 => 75
2 income 0 25 => 50
3 invoice 50 0 => 100

I changed the fields from mines, but as this table has more than
700.000 rows, I would like to put a LIMIT or WHERE clausule, but it
doesn't work ....

SELECT A.oid, A.detail, A.value_d, A.value_h
(sum( B.value_d )-sum(B.value_h)) AS value_sum
FROM assentaments AS A
INNER JOIN assentaments AS B
ON A.oid <= B.oid
GROUP BY A.oid, A.detail, A.value_d, A.value_h
ORDER BY A.oid

With this code it says: Error, Shcema 'a' doesn't exist ...

thanks for your help, really !

regards,

raimon fernandez