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