pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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)
>
>
>




view thread (4+ messages)  latest in thread

Message-ID: <001101c007eb$0cb37390$700c2304@francisco>
Permalink:  ../001101c007eb$0cb37390$700c2304@francisco/
Also on:    postgresql.org/message-id/001101c007eb$0cb37390$700c2304@francisco

 · 

reply

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