Received: from localhost.localdomain (lsanca1-ar1-013-179.dsl.gtei.net [4.35.13.179]) by hub.org (8.10.1/8.10.1) with ESMTP id e7H1XQm09862 for ; Wed, 16 Aug 2000 21:33:27 -0400 (EDT) Received: from francisco (lsanca1-ar1-012-112.dsl.gtei.net [4.35.12.112]) by localhost.localdomain (8.9.3/8.8.7) with SMTP id RAA14915; Wed, 16 Aug 2000 17:46:12 -0700 Message-ID: <001101c007eb$0cb37390$700c2304@francisco> From: "Francisco Hernandez" To: "Oliver Seidel" , , "Volker Paul" References: Subject: Re: car mileage summation / sledgehammer method Date: Wed, 16 Aug 2000 18:32:37 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200008/148 great! Thanks for taking the time to demonstrate this! ----- Original Message ----- From: "Oliver Seidel" To: ; "Francisco Hernandez" ; "Volker Paul" 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) > > >