Re: Calc

On Wednesday 15 October 2003 12:59 pm, Muhyiddin A.M Hayat wrote:
> Dear all,
>
> I have below table
>
> +-----+--------------------+---------------------+
>
>  | id   |    db                    |cr                          |
>
> +-----+--------------------+---------------------+
>
>  |1     |                            | 200                      |
>  |2     | 100                    |                              |
>  |3     | 100                    |                              |
>  |4     | 150                    |                              |
>  |5     |                            | 200                      |
>
> I Would like to calc that balance, and look like
>
> +-----+--------------------+---------------------+-----------+
>
>  | id   |    db                    |cr                          |
>  | bal        |
>
> +-----+--------------------+---------------------+-----------+
>
>  |1     |                            | 200                     
>  | |200       | 2     | 100                    |                   
>  |           |100       | 3     | 100                    |         
>  |                     |0            | 4     | 150                 
>  |   |                              |-150      | 5     |           
>  |                 | 200                      |50         |
>
> What can I do to get result like that

Something like this:

select id,db,cr,(select sum(cr-db) from calc sub where sub.id <= 
calc.id) from calc;

This of course assumes that ID indicates the correct order of the 
entries and it will blow up if you allow nulls for cr or db (which 
you shouldn't since that would literally be interpreted as "they 
withdrew 10 and deposited an unknown amount"). If you have null 
values already and they should be interpreted as 0 just do this:

select id, db, cr, (select sum(coalesce(cr,0)-coalesce(db,0)) from 
calc sub where sub.id <= calc.id) from calc;

I assume no responsibility for potential lack of scalability of this 
query. :) It's quite possible a faster solution exists - we'll see 
what comes up on the list.

Cheers,
Steve