(TOUGH) SQL for additive column?

[mcrbids]

Assume you want to make a checkbook manager. You want to, in the query, get a list of checks for march, and have account totals next to each item entry?

What's the best way to do this? Is there a function that can do this? I need results as follows, EG:

(D=Deposit)

Chk amount total

D $-100 $100
1 $20 $80
2 $19 $61
3 $4.20 $56.80

I'm using Postgres 7.1/7.2, and so far, running a perfect blank.

-Ben

[nnichols]

I can't think of any way of doing it in SQL but it would be easy to do on the application side.

The solution will depend on what data you are actually storing in your db. If you give explanation of table structure and how you arrive at starting value (balance) I will give you an example.

Hope I can help

[chriskl]

Easy - use a subselect.

eg. (pseudo code)

SELECT check_total, (SELECT SUM(amount) FROM checks c2 WHERE c2.check_id=c1.check_id) AS balance FROM checks c1 WHERE month='march';

The correlated subquery might be a little slow tho. You can speed it up by creating a view or temporary table containing the account balance and then joining your list of checks against that.

BTW, Postgres 7.3 was released yesterday.

Chris