Re: Advance SQL subquery

On Wed, Sep 23, 2009 at 8:13 AM, AnthonyV <avequeau(at)gmail(dot)com> wrote:
> Hello,
>
> I have a table like :
>
>   date        |    value
> -------------------------------
> 2009-09-19 |      1
> 2009-09-20 |      2
> 2009-09-21 |      6
> 2009-09-22 |      9
> 2009-09-23 |      1
>
> 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:
>
>   date        | sum_value
> -------------------------------
> 2009-09-19 |      1   (sum from 2009-09-17 to 2009-09-19)
> 2009-09-20 |      3   (sum from 2009-09-18 to 2009-09-20)
> 2009-09-21 |      9   (sum from 2009-09-19 to 2009-09-21)
> 2009-09-22 |      17   (sum from 2009-09-20 to 2009-09-22)
> 2009-09-23 |      16   (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.

select date, (select count(*) from foo where date between f.date - 1
and f.date + 1) from foo f;