pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAccumulative Queries?
2+ messages / 2 participants
[nested] [flat]
* Accumulative Queries?
@ 2002-11-30 14:40 Benjamin Smith <bens@effortlessis.com>
0 siblings, 1 reply; 2+ messages in thread
From: Benjamin Smith @ 2002-11-30 14:40 UTC (permalink / raw)
To: pgsql-sql; +Cc: Benjamin Smith <bens@effortlessis.com>
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. -
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Accumulative Queries?
@ 2002-12-02 19:01 Joel Burton <joel@joelburton.com>
parent: Benjamin Smith <bens@effortlessis.com>
0 siblings, 0 replies; 2+ messages in thread
From: Joel Burton @ 2002-12-02 19:01 UTC (permalink / raw)
To: Benjamin Smith <bens@effortlessis.com>; +Cc: pgsql-sql
On Sat, Nov 30, 2002 at 02:40:08PM -0800, Benjamin Smith wrote:
> 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?
create table checks (
id serial primary key,
num varchar unique,
"to" varchar,
amt real,
date date
);
insert into checks (num, "to", amt, date) values
(0,'deposit',100,'2002-01-01');
insert into checks (num, "to", amt, date) values
(0,'jack',40,'2002-02-01');
insert into checks (num, "to", amt, date) values
(101,'jack',40,'2002-02-01');
insert into checks (num, "to", amt, date) values
(102,'bob',20,'2002-02-01');
insert into checks (num, "to", amt, date) values
(103,'VOID',0,'2002-02-01');
insert into checks (num, "to", amt, date) values
(104,'jenny',10,'2002-03-01');
insert into checks (num, "to", amt, date) values
(104,'raul',10,'2002-03-02');
insert into checks (num, "to", amt, date) values
(105,'raul',10,'2002-03-02');
select *,
( select sum(amt)
from checks c2
where c2.id<=c1.id as c2)
from checks c1;
will give you the full accounting. To get just March, put a
where-date-between clause in both the outer and inner queries.
This will run slowly, though, for many transactions. Either consider:
* "closing" an account every month/quarter/year/whenever will the
aggregate-so-far, and having your query use that, and do the math from
that point onwards
* store the running balance in the table, and use triggers to keep it up
to date for inserts/updates/deletes
--
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-12-02 19:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-30 14:40 Accumulative Queries? Benjamin Smith <bens@effortlessis.com>
2002-12-02 19:01 ` Joel Burton <joel@joelburton.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox