Re: car mileage summation / sledgehammer method

great!

Thanks for taking the time to demonstrate this!
----- Original Message -----
From: "Oliver Seidel" <os10000@in-medias-res.com>
To: <pgsql-sql@hub.org>; "Francisco Hernandez" <xy0xy0@earthlink.net>;
"Volker Paul" <vpaul@dohle.com>
Sent: Wednesday, August 16, 2000 2:26 PM
Subject: 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-03 00:00:00+02 |  18
> (3 rows)
>
>
>