Re: car mileage summation / sledgehammer method

great!

Thanks for taking the time to demonstrate this!
----- Original Message -----
From: "Oliver Seidel" <os10000(at)in-medias-res(dot)com>
To: <pgsql-sql(at)hub(dot)org>; "Francisco Hernandez" <xy0xy0(at)earthlink(dot)net>;
"Volker Paul" <vpaul(at)dohle(dot)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)
>
>
>