From: Francisco Hernandez <xy0xy0@earthlink.net>
To: Oliver Seidel <os10000@in-medias-res.com>
To: pgsql-sql@hub.org
To: Volker Paul <vpaul@dohle.com>
Subject: Re: car mileage summation / sledgehammer method
Date: Wed, 16 Aug 2000 18:32:37 -0700
Message-ID: <001101c007eb$0cb37390$700c2304@francisco> (raw)
References: <Pine.LNX.4.10.10008162322580.6511-100000@delta.imr-dvlp.de>
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)
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: xy0xy0@earthlink.net, os10000@in-medias-res.com, pgsql-sql@hub.org, vpaul@dohle.com
Subject: Re: car mileage summation / sledgehammer method
In-Reply-To: <001101c007eb$0cb37390$700c2304@francisco>
References: <Pine.LNX.4.10.10008162322580.6511-100000@delta.imr-dvlp.de> <001101c007eb$0cb37390$700c2304@francisco>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox