Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 5895863483F
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 23 Sep 2009 14:03:36 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 44632-02 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 23 Sep 2009 17:03:28 +0000 (UTC)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by mail.postgresql.org (Postfix) with ESMTP id 7CBE7633814
	for <pgsql-sql@postgresql.org>; Wed, 23 Sep 2009 14:03:28 -0300 (ADT)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by news.hub.org (8.14.3/8.14.3) with ESMTP id n8NH3RTJ098314
	for <pgsql-sql@postgresql.org>; Wed, 23 Sep 2009 14:03:27 -0300 (ADT)
	(envelope-from news@news.hub.org)
Received: (from news@localhost)
	by news.hub.org (8.14.3/8.14.3/Submit) id n8NGXe8k085836
	for pgsql-sql@postgresql.org; Wed, 23 Sep 2009 13:33:40 -0300 (ADT)
	(envelope-from news)
From: Jim <shakahshakah@gmail.com>
X-Newsgroups: pgsql.sql
Subject: Re: SQL Subqueries on each result row
Date: Wed, 23 Sep 2009 09:33:39 -0700 (PDT)
Organization: http://groups.google.com
Lines: 72
Message-ID: 
 <d4bb176c-8e81-49bd-8160-e3b2a8eb3bd9@d23g2000vbm.googlegroups.com>
References: 
 <b4fa00ca-986f-4a57-8690-e7d234ba6ebd@o21g2000vbl.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Complaints-To: groups-abuse@google.com
Complaints-To: groups-abuse@google.com
Injection-Info: d23g2000vbm.googlegroups.com; posting-host=169.132.96.95;
	posting-account=udxU8AkAAABJ5kv08fqSgNLb8BU46vnH
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.3a1pre)
	Gecko/20090921 Minefield/3.7a1pre,gzip(gfe),gzip(gfe)
To: pgsql-sql@postgresql.org
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=BAYES_50=0.001
X-Spam-Level: 
X-Archive-Number: 200909/113
X-Sequence-Number: 33178

On Sep 23, 5:43=A0am, AnthonyV <avequ...@gmail.com> wrote:
> Hello,
>
> I have a table like :
>
> =A0 =A0date =A0 =A0 =A0 =A0| =A0 =A0value
> -------------------------------
> 2009-09-19 | =A0 =A0 =A01
> 2009-09-20 | =A0 =A0 =A02
> 2009-09-21 | =A0 =A0 =A06
> 2009-09-22 | =A0 =A0 =A09
> 2009-09-23 | =A0 =A0 =A01
>
> I'd like a request which gives me the sum of each last n days.
> For example, if I want the sum of each 3 days, I want this result:
>
> =A0 =A0date =A0 =A0 =A0 =A0| sum_value
> -------------------------------
> 2009-09-19 | =A0 =A0 =A01 =A0 (sum from 2009-09-17 to 2009-09-19)
> 2009-09-20 | =A0 =A0 =A03 =A0 (sum from 2009-09-18 to 2009-09-20)
> 2009-09-21 | =A0 =A0 =A09 =A0 (sum from 2009-09-19 to 2009-09-21)
> 2009-09-22 | =A0 =A0 =A017 =A0 (sum from 2009-09-20 to 2009-09-22)
> 2009-09-23 | =A0 =A0 =A016 =A0 (sum from 2009-09-21 to 2009-09-23)
>
> I try to make a subquery which is apply on each row of a query, but it
> does work.
>
> Has anybody an idea?
>
> Thanks in advance!
>
> Anthony

How about the following?

BEGIN ;

CREATE TABLE z (
  the_date   date not null
 ,value      integer not null
) ;

INSERT INTO z VALUES('2009-09-19',1) ;
INSERT INTO z VALUES('2009-09-20',2) ;
INSERT INTO z VALUES('2009-09-21',6) ;
INSERT INTO z VALUES('2009-09-22',9) ;
INSERT INTO z VALUES('2009-09-23',1) ;

SELECT z.the_date, SUM(z2.value)
  FROM z
       LEFT JOIN z z2
               ON z2.the_date IN (
              z.the_date
             ,z.the_date-'1 day'::interval
             ,z.the_date-'2 day'::interval
             )
 GROUP BY 1
 ORDER BY 1
;

ROLLBACK ;

output:

  the_date  | sum
------------+-----
 2009-09-19 |   1
 2009-09-20 |   3
 2009-09-21 |   9
 2009-09-22 |  17
 2009-09-23 |  16
(5 rows)