caculating while select - maybe sum ? 
Author Message
 caculating while select - maybe sum ?

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://www.***.com/
--------------------------

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Sat, 24 Jul 2004 23:07:51 GMT
 caculating while select - maybe sum ?

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


Quote:
>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
>--------------------------

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command



Sun, 25 Jul 2004 00:37:49 GMT
 caculating while select - maybe sum ?
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


Quote:
> 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


> >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
> >--------------------------

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly


Sun, 25 Jul 2004 00:47:56 GMT
 caculating while select - maybe sum ?

Quote:
> 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

---------------------------(end of broadcast)---------------------------



Sun, 25 Jul 2004 01:29:48 GMT
 caculating while select - maybe sum ?
... 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)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Sun, 25 Jul 2004 01:40:25 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. SELECT xxx = (SELECT SUM ...

2. Which is faster Sum(fld1) + Sum(fld2) +...Sum(fld50) -- OR this

3. Sum(Sum(Sum ...

4. Select statement - maybe Union/Join

5. Sum records from two tables in one SELECT statement

6. Using COUNTand SUM in a select

7. SELECT SUM(x)...what about PRODUCT(x)?

8. Summing Money vs Selecting with Imported data

9. HELP please with a SELECT, SUM, Aggregate, order and group by

10. select the top number records where the qty would sum to a given number

11. Sum Function in Select Statement with IF Condition

12. Select Sum


 
Powered by phpBB® Forum Software