Thread: car mileage summation / sledgehammer method

OK,

this is quite brutal and is going to be very expensive, but I think it
does what you want.  The trick lies in the idea of joining a table with
itself.  Thus, I first define a virtual copy of the table (in my case
"dup") and then produce a query that joins the table to this copy.  Enjoy.

Oliver


detail=# \d mileage        Table "mileage"Attribute |   Type    | Modifier 
-----------+-----------+----------miles     | integer   | date      | timestamp | 

detail=# select * from mileage;miles |          date          
-------+------------------------    5 | 2000-08-01 00:00:00+02    9 | 2000-08-02 00:00:00+02    4 | 2000-08-03
00:00:00+02
(3 rows)

detail=# \d dup           View "dup"Attribute |   Type    | Modifier 
-----------+-----------+----------miles     | integer   | date      | timestamp | 
View definition: SELECT mileage.miles, mileage.date FROM mileage;

detail=# select mileage.miles, mileage.date, sum(dup.miles) from mileage, dup where dup.date <= mileage.date group by
mileage.date,mileage.miles order by mileage.date;miles |          date          | sum 
 
-------+------------------------+-----    5 | 2000-08-01 00:00:00+02 |   5    9 | 2000-08-02 00:00:00+02 |  14    4 |
2000-08-0300:00:00+02 |  18
 
(3 rows)

> View definition: SELECT mileage.miles, mileage.date FROM mileage;

> detail=# select mileage.miles, mileage.date, sum(dup.miles) from
>          mileage, dup
>        where
>    dup.date <= mileage.date group by mileage.date, mileage.miles
>     order by mileage.date;

Now that's a bit stupid. Why create this view when you can do the same
thing with a table alias (I'm I calling this by the right name?).

select m.miles, m.date, sum(d.miles) from mileage m, mileage d
where.......

Maarten


-----------------------------------------------------------------       Visit our Internet site at
http://www.reuters.com

Any views expressed in this message are those of  the  individual
sender,  except  where  the sender specifically states them to be
the views of Reuters Ltd.

On Thu, Aug 17, 2000 at 01:55:19PM +0200, Maarten Boekhold wrote:
> 
> Now that's a bit stupid. Why create this view when you can do the same
> thing with a table alias (I'm I calling this by the right name?).

Now now, let's not call it stupid: how about 'not optimal'?

> 
> select m.miles, m.date, sum(d.miles) from mileage m, mileage d
> where.......
> 

This is the traditional way to do a self join, yes.

Ross
-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005