Postgres Pro
Downloads
Home   >   mailing lists

Thread: caculating while select - maybe sum ?

caculating while select - maybe sum ?

From
"Ben-Nes Michael"
Date:
Hi All

im trying to calculate hour usage so a typical row is: 1. enter time, 2.
exit time 3. sum() until this row

Couldn't use sum() as it ask me to 'group by' which I don't want.

Is there a way to add the previous row value to the current, per row ?

example:
select row1, row2, (row1 + row2 until now) from table;

--------------------------
Canaan Surfing Ltd.
Internet Service Providers
Ben-Nes Michael - Manager
Tel: 972-4-6991122
http://sites.canaan.co.il
--------------------------

Re: caculating while select - maybe sum ?

From
Frank Bax
Date:
I'm not quite sure what you're asking for; but it shoulds like you want:
    age(timestamp,timestamp) => interval
For example:
select age('2002-12-25',now());
           age
--------------------------
 10 mons 19 days 12:30:28
(1 row)

Frank

At 04:17 PM 2/5/02 +0200, Ben-Nes Michael wrote:
>Hi All
>
>im trying to calculate hour usage so a typical row is: 1. enter time, 2.
>exit time 3. sum() until this row
>
>Couldn't use sum() as it ask me to 'group by' which I don't want.
>
>Is there a way to add the previous row value to the current, per row ?
>
>example:
>select row1, row2, (row1 + row2 until now) from table;
>
>--------------------------
>Canaan Surfing Ltd.
>Internet Service Providers
>Ben-Nes Michael - Manager
>Tel: 972-4-6991122
>http://sites.canaan.co.il
>--------------------------

Re: caculating while select - maybe sum ?

From
Ben-Nes Michael
Date:
Nope, I mean:

a column from table:
1
2
7
4

select row1,  sum (row1 until current row) from table;

Result:
1 | 1
2 | 3
7 | 10
4 | 14

the second column is like sum()  of all the rows until/include this row.

Sorry for being ambigious

On Tuesday 05 February 2002 18:21, Frank Bax wrote:
> I'm not quite sure what you're asking for; but it shoulds like you want:
>     age(timestamp,timestamp) => interval
> For example:
> select age('2002-12-25',now());
>            age
> --------------------------
>  10 mons 19 days 12:30:28
> (1 row)
>
> Frank
>
> At 04:17 PM 2/5/02 +0200, Ben-Nes Michael wrote:
> >Hi All
> >
> >im trying to calculate hour usage so a typical row is: 1. enter time, 2.
> >exit time 3. sum() until this row
> >
> >Couldn't use sum() as it ask me to 'group by' which I don't want.
> >
> >Is there a way to add the previous row value to the current, per row ?
> >
> >example:
> >select row1, row2, (row1 + row2 until now) from table;
> >
> >--------------------------
> >Canaan Surfing Ltd.
> >Internet Service Providers
> >Ben-Nes Michael - Manager
> >Tel: 972-4-6991122
> >http://sites.canaan.co.il
> >--------------------------

Re: caculating while select - maybe sum ?

From
Thomas Lockhart
Date:
> select row1,  sum (row1 until current row) from table;
> the second column is like sum()  of all the rows until/include this row.

"Until this row" is not very specific. I'll assume that you are assuming
a time ordering for the rows, so that you are really saying that you
want the aggregate of something up to (and including?) the current
something.

Here is a little example of how you might do that:

lockhart=# create table t1 (i int, b timestamp, e timestamp);
CREATE
lockhart=# insert into t1 values (1, 'now', timestamp 'now' + '1 sec');
(repeat three times, slowly...)

lockhart=# create function xsum(timestamp)
lockhart-# returns int as 'select cast(sum(i) as int)
lockhart-# from t1 where b <= $1;' language 'sql';
CREATE
lockhart=# select *, xsum(b) from t1;
 i |           b            |           e            | xsum
---+------------------------+------------------------+------
 1 | 2002-02-05 17:14:37+00 | 2002-02-05 17:14:38+00 |    1
 1 | 2002-02-05 17:14:40+00 | 2002-02-05 17:14:41+00 |    2
 1 | 2002-02-05 17:14:41+00 | 2002-02-05 17:14:42+00 |    3
 1 | 2002-02-05 17:14:42+00 | 2002-02-05 17:14:43+00 |    4
(4 rows)

Or if you want to sum a difference of times, try

lockhart=# create function tsum(timestamp)
lockhart-# returns interval as 'select sum(e-b)
lockhart-# from t1 where b <= $1;' language 'sql';
CREATE
lockhart=# select *, tsum(b) from t1;
 i |           b            |           e            |   tsum
---+------------------------+------------------------+----------
 1 | 2002-02-05 17:14:37+00 | 2002-02-05 17:14:38+00 | 00:00:01
 1 | 2002-02-05 17:14:40+00 | 2002-02-05 17:14:41+00 | 00:00:02
 1 | 2002-02-05 17:14:41+00 | 2002-02-05 17:14:42+00 | 00:00:03
 1 | 2002-02-05 17:14:42+00 | 2002-02-05 17:14:43+00 | 00:00:04
(4 rows)

This is an expensive query! I'll bet you can recast your specification
to something simpler which doesn't require executing a subquery for
every row.

hth

                   - Thomas

Re: caculating while select - maybe sum ?

From
Thomas Lockhart
Date:
... and if you want to ditch the function call, you can try something
like this:

lockhart=# select *, (select sum(e-b) from t1 where b <= x.b) from t1 x;
 i |           b            |           e            | ?column?
---+------------------------+------------------------+----------
 1 | 2002-02-05 17:14:37+00 | 2002-02-05 17:14:38+00 | 00:00:01
 1 | 2002-02-05 17:14:40+00 | 2002-02-05 17:14:41+00 | 00:00:02
 1 | 2002-02-05 17:14:41+00 | 2002-02-05 17:14:42+00 | 00:00:03
 1 | 2002-02-05 17:14:42+00 | 2002-02-05 17:14:43+00 | 00:00:04
(4 rows)