[SQL] Accumulative Queries?

[Benjamin Smith]

Let's say you have a table of Financial transactions: 

Create table checks ( 
id serial, 
number varchar, 
to varchar, 
amount real, 
date integer 
); 

(date is an epoch timestamp) 

And you want to get a listing of checks 

"SELECT * FROM checks ORDER BY date ASC"; 

but you also want to have an accumulative field that adds up the amount field as the 
results are returned, so you might see results like: 

id number to amount date balance
1 0 Deposit -100 12344 100
2 100 Jack 40 123455 60
3 101 Bob 20 123345 40 
4 102 VOID 0 0 40
5 103 Harold 11 123488 29

Is this possible using only SQL? 

Also, assuming you have checks year round, how might you get results only in March 
that have totals consistent for the time frame while taking into account all the other 
checks in Jan and Feb? 

- The best way to predict the future is to invent it. -





---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] Join table with itself for heirarchial system?

[Benjamin Smith]

Can you query a set of nested entries to simulate a heirarchial system with a 
single query? 

I'm building a nested category table with a definition like below" 

CREATE TABLE category ( 
id serial, 
parent integer not null, 
title varchar); 

Idea is that we can "nest" categories so that we have 

id  parent  title
--
1   0   Clothing
2   1   Shirts
3   1   Pants
4   1   Socks
5   4   Male
6   4   Silk 

So that, for example, id 6 would be 

Clothing -> Socks -> Silk. 

So far, I've only been able to derive this with 3 queries - 1 to get the 
parent for id #6 (Silk) another to get the parent for id #4 (Socks) and 
finally for id #1 (Clothing) and since parent ==0 I stop. 

This seems wasteful - can this be done in a single query? 

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html